Opening File from custom toolbar button

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

Hi all,
I am absolutely perplexed!!
I have created a toolbar button with the following macro assigned:

Sub RecordsCurrent2()
Workbooks.Open "C:\Documents and Settings\User\My Documents\Records\Records
current2.xls"
End Sub

It runs fine on my Windows98/Office 2000 Desktop PC (with some minor
difference in path string)
BUT......
on a WindowsXP/Office97 Laptop, for some strange reason, the file opens,
without executing the workbook open() event in This Workbook.
The procedure that won't work can be as simple as :
Sheets ("Tax Invoice Records").Select
The workbook does open and no error messages appear, except that the
workbook open() code won't run.
If I change the code to MsgBox "Hello", then that DOES execute!???
THE STRANGE THING IS.............
If I open the workbook from within its folder or from the list in the
dropdown file menu, then it does run the code correctly. But what's wrong
with opening it with the button?????
AAAAAAHHHHHHHHHHHHHHHHHH!
Please help.
Rob
 
Try :-
Sheets ("Tax Invoice Records").Activate

or, more powerful (because it Activates the sheet & Selects in one
go):-
Application.Goto Reference:=Sheets("Tax Invoice Records").Range("A1")

I have given up trying to figure "why". Quicker to find another way.
Excel always seems to offer numerous alternatives. Of course, if these
don't work you will probably have to <grin>. Probably because poor old
97 doesn't understand 2000. As a rule write macros in the oldest
version.

Regards
BrianB
===========================
 
Thanks Brian.
I had tried
Sheets ("Tax Invoice Records").Activate
But now also tried
Application.Goto Reference:=Sheets("Tax Invoice Records").Range("A1")
without success.
I somehow think the problem's not with the code, as the code runs fine if I
open the file direct from the folder in My documents.
Opening it from a macro (via the button) however, seems to be the problem.
Can I reference it better within the button macro that opens the file in the
first place? (Although I think not.)
The perplexing thing with this is that if I run the macro beginning with the
Button macro from within VB to open the file using the F8 key to execute
each step, it opens the file and appears to perform each line in the
Workbook Open() code. Although each line is highlighted as if it's
performing it, nothing happens! It just reads the lines one by one and ends
without doing any of the instructions.
The instructions I want to perform are these:

Private Sub Workbook_Open()
Worksheets("Tax Invoice Records").Select
Application.Goto Reference:=Sheets("Tax Invoice Records").Range("A1")
If Sheets("Tax Invoice Records").Range("A1") = True Then
Range("B20").Activate
If Sheets("Tax Invoice Records").Range("A1") = True Then
ActiveWindow.FreezePanes = True
If Sheets("Tax Invoice Records").Range("A1") = False Then MsgBox "Please
check FIRST, that the figures in BLUE " & _
"(the Financial Year Ending Date, The Tax Rate " & _
"and GST rate) are correct on the Tax Invoice Records worksheet."
End Sub
Running this via the button macro won't even select the Tax Invoice Records
sheet!

The macro (for the button) is as follows:
Sub RecordsCurrent()
Workbooks.Open "C:\Documents and Settings\user\My
Documents\Records\Records (CURRENT YEAR).xls"
End Sub


(The rest of the code in the file it's opening is due to:-
As I'm using office 97, I need to have the workbook open without one of the
sheets having freeze panes activated. Freezing panes causes certain cells
above the freeze not to show the Data Validation drop-down (a bug in office
97), so I want to freeze panes after other events have occured.)

Maybe it's another bug in 97 as it works fine with office 2000 on my other
computer????
Rob
 
Back
Top