Just upgraded to Vista Macros unstable

MS EXCEL

    Sponsored Links

    Next

  • 1. Have macro force user to selecat a value
    Part of my macro now has this line. Range("D2").FormulaR1C1 = "CTR" It enters the value "CTR" in a cell. Now I need to modify it, so that it opens up a box in which the user can select a value from a list of 3 options. How do I do this? What code do I need?
  • 2. Access to Worksheet in AddIn.xla
    Greetings ! Earlier today I wrote in here >> ... I would like to create my own icons to go on other >> menu items. "Colo" kindly replied, 12/10/2003 1:32:47 AM, for which I am very grateful, > Please place Auto shpas or bitmaps in the sheet1 before run this code. > ..... > Set sh = Sheets(1) > ..... > Set shp = sh.Shapes(lngCnt) > ..... > shp.Copy > ..... > .PasteFace These routines all go into my AddIn.xla, which I notice has a Sheet1. My further question, then, is this - Can I put these shapes into that Sheet 1 in my AddIn.xla ? And if so, how ? "Normally", I can't access that worksheet in the .xla file. RClay AT haswell DOT com
  • 3. Apply Border based on last Day of Month
    I have a spreadsheet that list three months of dates. Row 3 list the day for each month. Example B3 = 1 C3 = 2 D3 = 3 E3 = 4......And so on... eventually CO3 = 31 I need to accomplish two tasks using VBA for Excel. 1. I need to apply a verticle line to the right side of the column when it equals the last day of the month. As an example... If the starting month is Feb 05, then a thick border would be applied to the right side of colum AC because AC3 contains the last day of the month (28) for Feb. Columns BH and CL would also have a thick border applied to the right side since BH3 and CL3 contain the last working day for those months.
  • 4. Flipping a name
    Try this function. Public Function FormatName(ByVal InputName As String) As String FormatName = Right(InputName, Len(InputName) - InStrRev(Trim(InputName), " ")) & _ ", " & Trim(Left(InputName, InStrRev(InputName, " "))) End Function Jim Thomlinson "Duncan_J" wrote: > If I got column of names > John Doe > > And what to change it to > Doe, John > > Is thier a way of doing this? > > DJ

Just upgraded to Vista Macros unstable

Postby Snowfire » Thu, 30 Aug 2007 06:53:58 GMT

When trying to run my well established macro driven applications on a
new vista computer I get problems with functions like Right, SendKeys
(to name just two) needing the prefix Application.
ie.    Application.Right( etc. or Application.Sendkeys etc. prefixed
to stop them crashing the macro. After a torturous path to get Vista
up and running all my programs this is all I DON'T need.....

Any help to fix this, short of rewriting too many to count Excel
programs, would be very much appreciated.

Mike


RE: Just upgraded to Vista Macros unstable

Postby VG9tIE9naWx2eQ » Thu, 30 Aug 2007 08:08:01 GMT

once you get the error, look in Tools=>References in the VBE (you may have to 
hit RESET first).  You should see one or more references marked as MISSING.  
Correct these. 

-- 
Regards,
Tom Ogilvy







Similar Threads:

1.Word 2007 unstable on Vista but not XP

I purchased a new Sony Vaio laptop last April.  I am on the verge of getting 
out the recovery dvds I created when I first bought it and starting over 
again. 
 It came with a trial version of Office 2007 which I tried.  It operated just 
fine so I purchased the small business version and installed that.  It wasn't 
long after that I noticed when opening a word doc or starting a new one that 
the rotating busy icon keeps running while word is open.  I can still type, 
edit, backspace and use word like normal except this causes a good amount of 
lag time on everything.  This problem also causes the taskbar (which I auto 
hide) to flash at the bottom which continues even after word is closed until 
re-starting the computer.  All the other Office products in the suite 
work fine.

I had heard about Vista causing problems with programs so I loaded Office 
2007 on my desktop running XP and I have no problems.  All updates to Vista 
and Office have been installed.  I was hoping one of the updates would fix 
the problem.  Office was uninstalled and re-installed on 
the Vista system with the exact same problem still showing up.  I have tried 
to Google this problem and not any luck finding a solution.  I saw Word 2007 
running fine on my style laptop at Best Buy the other day.

Has anyone else had this problem?

-- 
Thanks
John

2.documents unstable since i upgraded to office live

I installed Microsoft office Live  and since then my documents have become 
unstable in that text boxes cannot be entered and date entered in them 
the just come to the foreground and blank out any data entry info 

3.Can't find bug because program justs Stops !!

Hello All,

I've posted about this previously but still haven't solved the problem
- really banging my head against a brock wall!

I've got a large program which runs in a second instance of Excel. It
doesn't bug out but just stops part way though  - when I open the
second instance there will be a workbook open in design mode.

How do I go about finding the problem?

Any help much appreciated
Jason


'======================================
here's the code (....there's quite a bit!)..........


Option Explicit

Private Const mySummaryStem As String = "R:\Statistics\Reporting\Daily
Summary\Daily summary 0.4\"
Private Const myStorageFileStore As String = "R:\Statistics\Reporting
\Daily Summary\Daily summary 0.4\Data Storage Sheets 0.4\"
Private Const mySummaryFilePath As String = "R:\Statistics\Reporting
\DailySummary\Daily summary 0.4\Daily Casino Summary 0.4.xlsm"
Private Const myStorageTemplatePath As String = "R:\Statistics
\Reporting\Daily Summary\Daily summary 0.4\Daily Storage Template
0.4.xlsx"
Private Const myFeedFilePath As String = "R:\Statistics\Reporting
\Daily Summary\Daily summary 0.4\Daily Feed 0.4.xlsm"

Private myFeedBook As Workbook

Private rSource As Range
Private rDest As Range

Private AlreadyUpdated As Boolean
Private blUpdateAll As Boolean
Private blUpdateFormatting As Boolean

Private blSaveStorageSheet As Boolean

Private oPivCatRange As Range

'Private oItem
'Private oItem As String
Private myItem As String
Private myStorageName As String
Private EndCell As Integer
Private j As Integer


Private myLastRow
Private myStorageBook As Workbook
Private mySheet As Worksheet

Private mySummaryBook As Workbook
Private i As Integer


Public Sub UpdateFeedWorkbook()

Application.ScreenUpdating = False
      Set myFeedBook = Workbooks.Open(myFeedFilePath, , False, , , ,
True)

            With myFeedBook
                  .Sheets("Daily_QueryTable").ListObjects
(1).QueryTable.Refresh BackgroundQuery:=False
                  .Sheets("Pivot").PivotTables
("PivotTable3").PivotCache.Refresh
                  .Sheets("Pivot2").PivotTables
("PivotTable1").PivotCache.Refresh

                  Set rSource = .Sheets("Pivot2").Range("C5:C"
& .Sheets("Pivot2").Cells(.Sheets("Pivot2").Rows.Count, 3).End
(xlUp).Row)
                  Set rDest = ThisWorkbook.Sheets("Static_Data").Range
("S6")
                  With rSource
                        Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
                  End With
                  rDest.Value = rSource.Value

            End With

      Set myFeedBook = Nothing
Application.ScreenUpdating = True

End Sub
Public Sub UpdateStorageBooksAndSummary()



blUpdateAll = False
Application.ScreenUpdating = True
      If MsgBox("Do you wish to update all storage sheets irrespective
as to whether they have already been saved today?", vbYesNo +
vbDefaultButton2, "Overwrite Existing Files") = vbYes Then
            Application.ScreenUpdating = False
                  blUpdateAll = True
      End If
Application.ScreenUpdating = False

blUpdateFormatting = False
Application.ScreenUpdating = True
      If MsgBox("Do you wish to update sheet formatting?", vbYesNo +
vbDefaultButton2, "Update formatting") = vbYes Then
            Application.ScreenUpdating = False
                  blUpdateFormatting = True
      End If
Application.ScreenUpdating = False




'========open the summary file
      'open summary file
If IsFileOpen(ExtractFileName(mySummaryFilePath)) = False Then
      Workbooks.Open mySummaryFilePath, , False, , , , True
End If
Set mySummaryBook = Workbooks(ExtractFileName(mySummaryFilePath))
'========

      'clear out the data sheets that were previously collated from
the storage sheets
With mySummaryBook
      .Sheets("Data_Measures").Range("A2:AZ10000").ClearContents
      .Sheets("Data_MaxMin").Range("A2:AZ10000").ClearContents
      .Sheets("Data_Graphs").Range("A4:G10000").ClearContents
      .Sheets("Data_Graphs").Range("J4:M10000").ClearContents
      .Sheets("Data_Graphs").Range("P4:R10000").ClearContents
End With
'========



'========open the feed file
      'open feed file
If IsFileOpen(ExtractFileName(myFeedFilePath)) = False Then
      Workbooks.Open myFeedFilePath, , False, , , , True
End If
Set myFeedBook = Workbooks(ExtractFileName(myFeedFilePath))
'========



'========open all storage sheets
      'look at the category names in the pivot on the Control sheet
'With ThisWorkbook.Sheets("Static_Data")
'      Set oPivCatRange = .Range("StorageSheetsToUpdate")
'End With

i = 1

EndCell = ThisWorkbook.Sheets("Static_Data").Range("C100").End
(xlUp).Row

      'loop through the category names, which correspond to the
storage book names
'For Each oItem In oPivCatRange.Cells
For j = 6 To EndCell

            myItem = ThisWorkbook.Sheets("Static_Data").Cells(j,
3).Value
            myStorageName = myItem & ".xlsx"


            If myItem <> "" Then

                              'check if NOT saved today;
                        AlreadyUpdated = False
                        If FileDateTime(myStorageFileStore &
myStorageName) > Date And blUpdateAll = False Then
                                   AlreadyUpdated = True
                        End If


                        '=======open each Storage book - always opens
file to move data to summary
                        Set myStorageBook = Workbooks.Open
(myStorageFileStore & myStorageName)    ', , False, , , , True


                        '=======clear out old data if not already
updated
                        If AlreadyUpdated = True Then
                        Else
                              With myStorageBook.Sheets("Input")
                                    .Range("C6:AZ500").ClearContents
                                    .Range("D2").ClearContents
                              End With
                        End If
                        '=========================================



                        '=======copy data into Storage sheet
                        If AlreadyUpdated = True Then
                        Else
                              With myFeedBook.Sheets("Pivot")

                                    Application.ScreenUpdating =
True         '#########################NEW 21AUG09
                                    .Range("E3").Value = myItem
                                    Application.ScreenUpdating =
False        '#########################NEW 21AUG09

                                    myLastRow = .Cells(Rows.Count,
4).End(xlUp).Row

                                    Set rSource = .Range("D7:D" &
myLastRow)
                                    Set rDest = myStorageBook.Sheets
("Input").Range("C7")
                                    With rSource
                                          Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
                                    End With
                                    rDest.Value = rSource.Value

                                    Set rSource = .Range("B6:B" &
myLastRow)
                                    Set rDest = myStorageBook.Sheets
("Input").Range("D6")
                                    With rSource
                                          Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
                                    End With
                                    rDest.Value = rSource.Value

                                    Set rSource = .Range("E6:AZ" &
myLastRow)
                                    Set rDest = myStorageBook.Sheets
("Input").Range("E6")
                                    With rSource
                                          Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
                                    End With
                                    rDest.Value = rSource.Value

                                    Set rSource =
Nothing                                 '#########################NEW
19AUG09
                                    Set rDest =
Nothing
'#########################NEW 19AUG09

                              End With
                        End If
                        '=========================================



                        '=======copy data out of Storage
sheet==========
                        With Workbooks(myStorageName).Sheets
("Summary")
                              .Activate

                              Set rSource = .Range("C5:BG" & .Range
("B46").Value + 4)
                              Set rDest = mySummaryBook.Sheets
("Data_Measures").Cells(Rows.Count, 4).End(xlUp)(2, 1)
                              With rSource
                                    Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
                              End With
                              rDest.Value = rSource.Value

                              Set rSource =
Nothing                                 '#########################NEW
19AUG09
                              Set rDest =
Nothing
'#########################NEW 19AUG09

                        End With
                        With mySummaryBook.Sheets("Data_Measures")
                              .Range("B" & .Cells(.Rows.Count, 2).End
(xlUp).Row + 1 & ":B" & .Cells(.Rows.Count, 4).End(xlUp).Row) =
Workbooks(myStorageName).Sheets("Summary").Range("C2").Value
                              .Range("C" & .Cells(.Rows.Count, 3).End
(xlUp).Row + 1 & ":C" & .Cells(.Rows.Count, 4).End(xlUp).Row) = myItem
                        End With
                         '=======

                         'copy graph data out of Storage sheet
                        With Workbooks(myStorageName).Sheets("All
Operator")
                              .Activate
'#########################NEW 19AUG09

                              Application.ScreenUpdating =
True              '#########################NEW 21AUG09

                                    Set rSource = .Range("AH7:AL43")
                                    Set rDest = mySummaryBook.Sheets
("Data_Graphs").Cells(mySummaryBook.Sheets("Data_Graphs").Rows.Count,
3).End(xlUp)(2, 1)
                                    With rSource
                                          Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
                                    End With
                                    rDest.Value = rSource.Value

                                    Set rSource = .Range("AJ6:AL6")
                                    Set rDest = mySummaryBook.Sheets
("Data_Graphs").Cells(mySummaryBook.Sheets("Data_Graphs").Rows.Count,
11).End(xlUp)(2, 1)
                                    With rSource
                                          Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
                                    End With
                                    rDest.Value = rSource.Value

                                    Set rSource = .Range("Y6:Z136")
                                    Set rDest = mySummaryBook.Sheets
("Data_Graphs").Cells(mySummaryBook.Sheets("Data_Graphs").Rows.Count,
17).End(xlUp)(2, 1)
                                    With rSource
                                          Set rDest = rDest.Resize
(.Rows.Count, .Columns.Count)
                                    End With
                                    rDest.Value = rSource.Value

                                    Set rSource =
Nothing                                 '#########################NEW
19AUG09
                                    Set rDest =
Nothing
'#########################NEW 19AUG09

                              Application.ScreenUpdating =
False              '#########################NEW 21AUG09

                        End With

                        With mySummaryBook.Sheets("Data_Graphs")
                              .Range("B" & .Cells(.Rows.Count, 2).End
(xlUp).Row + 1 & ":B" & .Cells(.Rows.Count, 3).End(xlUp).Row) = myItem
                              .Range("J" & .Cells(.Rows.Count, 10).End
(xlUp).Row + 1 & ":J" & .Cells(.Rows.Count, 11).End(xlUp).Row) =
myItem
                              .Range("P" & .Cells(.Rows.Count, 16).End
(xlUp).Row + 1 & ":P" & .Cells(.Rows.Count, 17).End(xlUp).Row) =
myItem
                        End With
                        '=========================================




                        '=======format each sheet in data storage
book==========
                        If AlreadyUpdated = True Then
                        Else

                              If blUpdateFormatting = True Then
'#########################NEW 20AUG09
                                    myStorageBook.Activate

      '                              On Error Resume Next
                                          For Each mySheet In
myStorageBook.Worksheets

                                                      'check to see if
the storage sheet is being used
                                                      'if it isn't
then delete it
                                                If mySheet.Name <>
"Input" And mySheet.Name <> "Summary" Then
                                                      With mySheet
                                                            .Activate
 
'                                                      .Calculate
                                                      End With
                                                      If mySheet.Range
("C2").Value = "Empty" Then
 
Application.DisplayAlerts = False
 
mySheet.Delete
 
Application.DisplayAlerts = True
                                                      Else
 
mySheet.Range
("D:G,J:L,N:N,O:P,T:T,Z:AB,AJ:AL,AO:AQ,AU:AV").EntireColumn.AutoFit
                                                      End If
                                                End If
                                          Next
                              End If
'#########################NEW 20AUG09
'                              On Error GoTo 0

'                              myStorageBook.Sheets("All
Operator").Activate
                        End If


                        '=====only save the storage sheets if
necessary========
                        If AlreadyUpdated = True Then
                                    myStorageBook.Close False
                        Else
                                    myStorageBook.Close True
                        End If
                        Set myStorageBook = Nothing
                        '=======
             End If
Next j
'========


Set myStorageBook = Nothing   '++++++++new


'========tidy up the summary file and then close it
With mySummaryBook.Sheets("Data_Measures")
      .Range("A2").FormulaR1C1 = "=RC[1]&RC[2]&RC[3]"
      .Range("A2").AutoFill Destination:=.Range("A2:A" & .Cells
(.Rows.Count, 2).End(xlUp).Row)

      Set rSource = .Range("A2:A" & .Cells(.Rows.Count, 2).End
(xlUp).Row)
      Set rDest = .Range("A2:A" & .Cells(.Rows.Count, 2).End
(xlUp).Row)
      With rSource
            Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
      End With
      rDest.Value = rSource.Value

End With


With mySummaryBook.Sheets("Data_Graphs")
      .Range("A4").FormulaR1C1 = "=RC[1]&RC[2]"
      .Range("A4").AutoFill Destination:=.Range("A4:A" & .Cells
(.Rows.Count, 2).End(xlUp).Row)

      Set rSource = .Range("A4:A" & .Cells(.Rows.Count, 2).End
(xlUp).Row)
      Set rDest = .Range("A4:A" & .Cells(.Rows.Count, 2).End
(xlUp).Row)
      With rSource
            Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
      End With
      rDest.Value = rSource.Value

End With


With mySummaryBook.Sheets("Data_Available")
      .Range("F4:F100").ClearContents
      .PivotTables("PivotTable2").PivotCache.Refresh
      Set rSource = .Range(.Cells(5, 14), .Cells(.Cells(.Rows.Count,
14).End(xlUp).Row, 14))
      Set rDest = .Range("F4")
End With
With rSource
      Set rDest = rDest.Resize(.Rows.Count, .Columns.Count)
End With
rDest.Value = rSource.Value



mySummaryBook.Sheets("Data_Available").PivotTables
("PivotTable1").PivotCache.Refresh
mySummaryBook.Sheets(1).Activate
mySummaryBook.Close True
'===========



'===========
Workbooks(ExtractFileName(myFeedFilePath)).Close False
ThisWorkbook.Sheets("Static_Data").Activate


Set rSource = Nothing  '=+++++++++
Set rDest = Nothing '++++++++++++
Set mySummaryBook = Nothing
Set oPivCatRange = Nothing


End Sub
Private Function IsFileOpen(strFile As String) As Boolean

Dim aName As String

On Error GoTo NotOpen:
    aName = Workbooks(strFile).Name
    IsFileOpen = True
    GoTo FunctionEnd:
NotOpen:
    IsFileOpen = False
FunctionEnd:

End Function            'IsFileOpen
Public Function ExtractFileName(ByVal strFullName As String) As String

Dim p As Integer
Dim i As Integer
Dim s As Integer

i = 1
Do
    p = InStr(i, strFullName, "\", 1)
    If p = 0 Then Exit Do
    s = p
    i = p + 1
Loop
s = s + 1
ExtractFileName = Mid(strFullName, s, Len(strFullName))

End Function





4.Upgraded from Vista to Windows 7

Hello, I recently upgraded from Vista to Windows 7.  However, when using 
Excel 2007 version, I notice that recent files that are displayed aren't 
accessible.  How do I get rid of them?  When I right click on any of them, 
there is no "delete" option.  Why isn't there a delete file short cut 
available?

5.Office app icons not associated with apps vista upgrade, office 20

Good morning;

I have encountered a problem after upgrading to Vista Ultimate. I installed 
my Office 2003 suite and the icons for each application are no longer 
associated with the application. I can still open them as their native app 
but it is dificult to easily distinguish between word, powerpoint, access, 
excel, etc. they all look the same except for their extensions. How do I get 
the association back.
Regards
Phil

6. Upgrade to VISTA -- Switch to Excel?

7. Installing Upgrade of Office 2003 on Vista (from 97 Full Version)

8. Installing Upgrade of Office 2003 on Vista (from 97 Full Versi



Return to MS EXCEL

 

Who is online

Users browsing this forum: No registered users and 93 guest