R
Richard
Hello,
Could anyone lend me some assistance with this most
exasperating problem. I've tried so many ways I'm getting
totally confused and it's time to call in help.
In my workbook I have two sheets. SheetA and SheetB.
Each sheet was developed independently, with SheetA
containing the main application and mostly all the macros.
SheetB has only a CommandButton1 macro and this macro
updates external data. There is also another
CommandButton2 on this sheet but not in use. Some of the
cells are linked between the sheets. All of the macros and
commands work without bugs and calculate the sheets as
required.
What I need, is the ability to have just SheetA active and
run the CommandButton1 contained in SheetB whilst I am
sitting in SheetA. In other words, drive my whole
application from SheetA only. I can't seem to get this to
happen no matter what I try. I just thought I could create
a macro whilst in SheetA and copy the code linked to
CommandButton1 in SheetB and that would be the end of it.
But this does not work. The CommandButton1 macro executes
in SheetA, and as the first few lines of code delete
cells, it wipes out most of my SpreadsheetA and then
crashes.
As I don't fully understand the CommandButton creation, I
just seem to be going round in circles. Think I'm confused
between Commands and macros. Could anyone be of assistance
to resolve this particularly frustrating problem. Come
back if I have not described the problem sufficiently well
or any other info is required.
This is what I've done:-
In SheetA, clicked Design Mode and opened control toolbox.
Selected the "rectangle" control tool and positioned it on
the sheet.
Double Clicked it and it took me to a new CommandButton3
and an empty Sub.
Activated SheetB and clicked on the CommandButton1 to take
me to it's code.
Copied this code to the empty Sub in CommandButton3 above.
Back in to SheetA and clicked on the CommandButton3 which
I had previously created in SheetA.
The macro immediately crashed at a line in my code in
CommandButton3 with a message of
"Application-defined or Object-defined error", and my data
on SheetA is deleted. (The area is just highlighted
black). The line of code that it stops on is
Selection.Querytable.Delete but when the same code is run
from SheetB it runs perfectly.
Surely, a macro created in different sheets can be run
from another sheet in the same workbook!
Sub CommandButton1_Click()
Application.ScreenUpdating = False
Range("BR2:BW3").Select
Selection.Clear
Range("BR8:BW9").Select
Selection.Clear
Columns("A:AY").Select
Range("AY1").Activate
Selection.Clear
Selection.QueryTable.Delete ' Crashes here!!!!
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\M01.iqy", Destination:=Range("A1"))
..FieldNames = False
..RefreshStyle = xlInsertDeleteCells
..RowNumbers = False
..FillAdjacentFormulas = False
..RefreshOnFileOpen = False
..HasAutoFormat = True
..BackgroundQuery = True
..TablesOnlyFromHTML = True
..Refresh BackgroundQuery:=False
..SavePassword = False
..SaveData = True
End With
The rest of the code just extracts the data and formats
the sheet.
Help please!
Richard
Could anyone lend me some assistance with this most
exasperating problem. I've tried so many ways I'm getting
totally confused and it's time to call in help.
In my workbook I have two sheets. SheetA and SheetB.
Each sheet was developed independently, with SheetA
containing the main application and mostly all the macros.
SheetB has only a CommandButton1 macro and this macro
updates external data. There is also another
CommandButton2 on this sheet but not in use. Some of the
cells are linked between the sheets. All of the macros and
commands work without bugs and calculate the sheets as
required.
What I need, is the ability to have just SheetA active and
run the CommandButton1 contained in SheetB whilst I am
sitting in SheetA. In other words, drive my whole
application from SheetA only. I can't seem to get this to
happen no matter what I try. I just thought I could create
a macro whilst in SheetA and copy the code linked to
CommandButton1 in SheetB and that would be the end of it.
But this does not work. The CommandButton1 macro executes
in SheetA, and as the first few lines of code delete
cells, it wipes out most of my SpreadsheetA and then
crashes.
As I don't fully understand the CommandButton creation, I
just seem to be going round in circles. Think I'm confused
between Commands and macros. Could anyone be of assistance
to resolve this particularly frustrating problem. Come
back if I have not described the problem sufficiently well
or any other info is required.
This is what I've done:-
In SheetA, clicked Design Mode and opened control toolbox.
Selected the "rectangle" control tool and positioned it on
the sheet.
Double Clicked it and it took me to a new CommandButton3
and an empty Sub.
Activated SheetB and clicked on the CommandButton1 to take
me to it's code.
Copied this code to the empty Sub in CommandButton3 above.
Back in to SheetA and clicked on the CommandButton3 which
I had previously created in SheetA.
The macro immediately crashed at a line in my code in
CommandButton3 with a message of
"Application-defined or Object-defined error", and my data
on SheetA is deleted. (The area is just highlighted
black). The line of code that it stops on is
Selection.Querytable.Delete but when the same code is run
from SheetB it runs perfectly.
Surely, a macro created in different sheets can be run
from another sheet in the same workbook!
Sub CommandButton1_Click()
Application.ScreenUpdating = False
Range("BR2:BW3").Select
Selection.Clear
Range("BR8:BW9").Select
Selection.Clear
Columns("A:AY").Select
Range("AY1").Activate
Selection.Clear
Selection.QueryTable.Delete ' Crashes here!!!!
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\M01.iqy", Destination:=Range("A1"))
..FieldNames = False
..RefreshStyle = xlInsertDeleteCells
..RowNumbers = False
..FillAdjacentFormulas = False
..RefreshOnFileOpen = False
..HasAutoFormat = True
..BackgroundQuery = True
..TablesOnlyFromHTML = True
..Refresh BackgroundQuery:=False
..SavePassword = False
..SaveData = True
End With
The rest of the code just extracts the data and formats
the sheet.
Help please!
Richard