|
What am I missing here....?
why doesn't this code work on a command button, but works as sub?
---------------------------------- Fails at Range("A1").Select Private Sub CommandButton1_Click() ' Application.ScreenUpdating = False ' SHOWS ONLY SHEETS "A" For Each sh In Sheets sh.Visible = True Next sh Sheets(Array("A 01", "A 02", "B 01", "B 02")).Select ActiveWindow.SelectedSheets.Visible = False Sheets("A 01").Visible = True Sheets("A 02").Visible = True Sheets("A 01").Select Range("A1").Select ' Application.ScreenUpdating = True End Sub --------------------------------------------- Yet this works via MacroRun Sub Macro1() For Each sh In Sheets sh.Visible = True Next sh Sheets(Array("A 01", "A 02", "B 01", "B 02")).Select ActiveWindow.SelectedSheets.Visible = False Sheets("A 01").Visible = True Sheets("A 02").Visible = True Sheets("A 01").Select Range("A1").Select End Sub ---------------------------------------------- This selects cell A1 on Sheet "A 01" Does this have something to do with the command button still be selected? How to get around this? Thanks for your input. Bruce -- The older I get, the better I used to be. |
What am I missing here....?
Bruce-
I can't explain why the sub code works while the button code does not, but if you replace "Range("A1").Select" with "Activesheet.Range("A1").Select" your button code will work. -Stan Shoemaker Palo Alto, CA "swatsp0p" wrote: why doesn't this code work on a command button, but works as sub? ---------------------------------- Fails at Range("A1").Select Private Sub CommandButton1_Click() ' Application.ScreenUpdating = False ' SHOWS ONLY SHEETS "A" For Each sh In Sheets sh.Visible = True Next sh Sheets(Array("A 01", "A 02", "B 01", "B 02")).Select ActiveWindow.SelectedSheets.Visible = False Sheets("A 01").Visible = True Sheets("A 02").Visible = True Sheets("A 01").Select Range("A1").Select ' Application.ScreenUpdating = True End Sub --------------------------------------------- Yet this works via MacroRun Sub Macro1() For Each sh In Sheets sh.Visible = True Next sh Sheets(Array("A 01", "A 02", "B 01", "B 02")).Select ActiveWindow.SelectedSheets.Visible = False Sheets("A 01").Visible = True Sheets("A 02").Visible = True Sheets("A 01").Select Range("A1").Select End Sub ---------------------------------------------- This selects cell A1 on Sheet "A 01" Does this have something to do with the command button still be selected? How to get around this? Thanks for your input. Bruce -- The older I get, the better I used to be. |
What am I missing here....?
Private Sub CommandButton1_Click()
' Application.ScreenUpdating = False ' SHOWS ONLY SHEETS "A" For Each sh In Sheets sh.Visible = True Next sh Sheets(Array("A 01", "A 02", "B 01", "B 02")).Select ActiveWindow.SelectedSheets.Visible = False Sheets("A 01").Visible = True Sheets("A 02").Visible = True Sheets("A 01").Select Sheets("A 01").(Range("A1").Select ' Application.ScreenUpdating = True End Sub an unqualified range refers to the sheet containing the code when housed in a sheet module. Since this isn't the activesheet - it can not be selected. Specifying which sheet by qualifying it solves the problem. -- Regards, Tom Ogilvy "swatsp0p" wrote in message ... why doesn't this code work on a command button, but works as sub? ---------------------------------- Fails at Range("A1").Select Private Sub CommandButton1_Click() ' Application.ScreenUpdating = False ' SHOWS ONLY SHEETS "A" For Each sh In Sheets sh.Visible = True Next sh Sheets(Array("A 01", "A 02", "B 01", "B 02")).Select ActiveWindow.SelectedSheets.Visible = False Sheets("A 01").Visible = True Sheets("A 02").Visible = True Sheets("A 01").Select Range("A1").Select ' Application.ScreenUpdating = True End Sub --------------------------------------------- Yet this works via MacroRun Sub Macro1() For Each sh In Sheets sh.Visible = True Next sh Sheets(Array("A 01", "A 02", "B 01", "B 02")).Select ActiveWindow.SelectedSheets.Visible = False Sheets("A 01").Visible = True Sheets("A 02").Visible = True Sheets("A 01").Select Range("A1").Select End Sub ---------------------------------------------- This selects cell A1 on Sheet "A 01" Does this have something to do with the command button still be selected? How to get around this? Thanks for your input. Bruce -- The older I get, the better I used to be. |
What am I missing here....?
Thanks to stanshoe and Tom O. for their help. I was missing the part that
the commandbutton is looking at the sheet housing the code. Simply selecting the sheet didn't work. Now it works as desired. Thanks again. "Tom Ogilvy" wrote: Private Sub CommandButton1_Click() ' Application.ScreenUpdating = False ' SHOWS ONLY SHEETS "A" For Each sh In Sheets sh.Visible = True Next sh Sheets(Array("A 01", "A 02", "B 01", "B 02")).Select ActiveWindow.SelectedSheets.Visible = False Sheets("A 01").Visible = True Sheets("A 02").Visible = True Sheets("A 01").Select Sheets("A 01").(Range("A1").Select ' Application.ScreenUpdating = True End Sub an unqualified range refers to the sheet containing the code when housed in a sheet module. Since this isn't the activesheet - it can not be selected. Specifying which sheet by qualifying it solves the problem. -- Regards, Tom Ogilvy "swatsp0p" wrote in message ... why doesn't this code work on a command button, but works as sub? ---------------------------------- Fails at Range("A1").Select Private Sub CommandButton1_Click() ' Application.ScreenUpdating = False ' SHOWS ONLY SHEETS "A" For Each sh In Sheets sh.Visible = True Next sh Sheets(Array("A 01", "A 02", "B 01", "B 02")).Select ActiveWindow.SelectedSheets.Visible = False Sheets("A 01").Visible = True Sheets("A 02").Visible = True Sheets("A 01").Select Range("A1").Select ' Application.ScreenUpdating = True End Sub --------------------------------------------- Yet this works via MacroRun Sub Macro1() For Each sh In Sheets sh.Visible = True Next sh Sheets(Array("A 01", "A 02", "B 01", "B 02")).Select ActiveWindow.SelectedSheets.Visible = False Sheets("A 01").Visible = True Sheets("A 02").Visible = True Sheets("A 01").Select Range("A1").Select End Sub ---------------------------------------------- This selects cell A1 on Sheet "A 01" Does this have something to do with the command button still be selected? How to get around this? Thanks for your input. Bruce -- The older I get, the better I used to be. |
| All times are GMT +1. The time now is 10:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com