Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Click Help
I am using MS 2003 and these are password protected sheets. I have a macro
that when a user clicks this particular button it creates 4 extra duplicates (Extra Earned Income Methd 1) of the same spread sheet and inserts the grand total from the extra worksheet on to a master grand total worksheet (family totals) at the end of the workbook. What I want is that when the user clicks the command button that just one extra duplicate is created, and the button could be clicked up to four times. That way if they are not going to use all of them there are not so many open tabs. Any ideas?? Below is my macro. Thank you in advance for any help. ~Roxy Private Sub CommandButton1_Click() Worksheets("Extra Earned Income Methd 1").Visible = True Sheets("Extra Earned Income Methd 1").Select Worksheets("Extra Earned Income Methd 1").Visible = True Worksheets("family totals").Range("A5").EntireRow.Hidden = False Worksheets("Extra Earned Income Methd 1 (2)").Visible = True Sheets("Extra Earned Income Methd 1 (2)").Select Worksheets("Extra Earned Income Methd 1 (2)").Visible = True Worksheets("family totals").Range("A6").EntireRow.Hidden = False Worksheets("Extra Earned Income Methd 1 (3)").Visible = True Sheets("Extra Earned Income Methd 1 (3)").Select Worksheets("Extra Earned Income Methd 1 (3)").Visible = True Worksheets("family totals").Range("A7").EntireRow.Hidden = False Worksheets("Extra Earned Income Methd 1 (4)").Visible = True Sheets("Extra Earned Income Methd 1 (4)").Select Worksheets("Extra Earned Income Methd 1 (4)").Visible = True Worksheets("family totals").Range("A8").EntireRow.Hidden = False Sheets("Extra Earned Income Methd 1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Click Help
Private Sub CommandButton1_Click() Worksheets("Extra Earned Income Methd 1").Copy _ after:=Worksheets("Extra Earned Income Methd 1") End Sub "Roxy" wrote: I am using MS 2003 and these are password protected sheets. I have a macro that when a user clicks this particular button it creates 4 extra duplicates (Extra Earned Income Methd 1) of the same spread sheet and inserts the grand total from the extra worksheet on to a master grand total worksheet (family totals) at the end of the workbook. What I want is that when the user clicks the command button that just one extra duplicate is created, and the button could be clicked up to four times. That way if they are not going to use all of them there are not so many open tabs. Any ideas?? Below is my macro. Thank you in advance for any help. ~Roxy Private Sub CommandButton1_Click() Worksheets("Extra Earned Income Methd 1").Visible = True Sheets("Extra Earned Income Methd 1").Select Worksheets("Extra Earned Income Methd 1").Visible = True Worksheets("family totals").Range("A5").EntireRow.Hidden = False Worksheets("Extra Earned Income Methd 1 (2)").Visible = True Sheets("Extra Earned Income Methd 1 (2)").Select Worksheets("Extra Earned Income Methd 1 (2)").Visible = True Worksheets("family totals").Range("A6").EntireRow.Hidden = False Worksheets("Extra Earned Income Methd 1 (3)").Visible = True Sheets("Extra Earned Income Methd 1 (3)").Select Worksheets("Extra Earned Income Methd 1 (3)").Visible = True Worksheets("family totals").Range("A7").EntireRow.Hidden = False Worksheets("Extra Earned Income Methd 1 (4)").Visible = True Sheets("Extra Earned Income Methd 1 (4)").Select Worksheets("Extra Earned Income Methd 1 (4)").Visible = True Worksheets("family totals").Range("A8").EntireRow.Hidden = False Sheets("Extra Earned Income Methd 1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Click Help
Is that all? I took out my entire code in inserted yours and now when I
click the button it just bumps me over to the next tab which is a completely different worksheet...and no new one appears. Am I missing something? Many thanks! ~Roxy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Click Help
The code is copying everything on the "Extra Earned Income Methd 1"
worksheet. if you have a blank worksheet the the copied sheet will also be blank. If no new sheets appear then I think you need to look at all you worksheets by using the arrows at the bottom left corner of the window. You probably have some sheets tabs that you are not seeing. "Roxy" wrote: Is that all? I took out my entire code in inserted yours and now when I click the button it just bumps me over to the next tab which is a completely different worksheet...and no new one appears. Am I missing something? Many thanks! ~Roxy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Click Help
Oops sorry I guess I wasn't clear enough, I have already created the 4 extra
sheets. They are hidden until the user clicks the button. Is there a way to differentiate 'click number one' from 'click number two'? Right now I have changed my macro so that when a user clicks the button just once the "Extra Earned Income Meth 1" shows. But can the user go back click the same button again, and get my already created sheet "Extra Earned Income Meth 1 (2)" sheet to be visible, and so on for all the sheets? This is what I've got so far, but it doesn't do anything if you try a second click. I've renamed the Private Sub's even though there is only 1 CommandButton cause I kept getting an error...... Please let me know if I don't make any sense macros/codes are still new to me :) Private Sub CommandButton1_Click() Worksheets("Extra Earned Income Methd 1").Visible = True Sheets("Extra Earned Income Methd 1").Select Worksheets("Extra Earned Income Methd 1").Visible = True Worksheets("family totals").Range("A5").EntireRow.Hidden = False End Sub Private Sub CommandButton2_Click() Worksheets("Extra Earned Income Methd 1 (2)").Visible = True Sheets("Extra Earned Income Methd 1 (2)").Select Worksheets("Extra Earned Income Methd 1 (2)").Visible = True Worksheets("family totals").Range("A6").EntireRow.Hidden = False End Sub Private Sub CommandButton3_Click() Worksheets("Extra Earned Income Methd 1 (3)").Visible = True Sheets("Extra Earned Income Methd 1 (3)").Select Worksheets("Extra Earned Income Methd 1 (3)").Visible = True Worksheets("family totals").Range("A7").EntireRow.Hidden = False End Sub Private Sub CommandButton4_Click() Worksheets("Extra Earned Income Methd 1 (4)").Visible = True Sheets("Extra Earned Income Methd 1 (4)").Select Worksheets("Extra Earned Income Methd 1 (4)").Visible = True Worksheets("family totals").Range("A8").EntireRow.Hidden = False Sheets("Extra Earned Income Methd 1").Select End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Click Help
Does this code help???
Private Sub CommandButton1_Click() SheetName = ActiveSheet.Name 'check if there is a parenthesis in sheet name 'No parenthesis then version 1 If InStr(SheetName, "(") = 0 Then BaseName = SheetName Version = 1 Else 'BaseName is sheet name left of parenthesis 'Use trim to remove space at end of name BaseName = Trim(Left(SheetName, InStr(SheetName, "(") - 1)) 'version is number after parenthis Version = Mid(SheetName, InStr(SheetName, "(") + 1) 'remove closing parenthisis Version = Val(Trim(Left(Version, _ InStr(Version, ")") - 1))) End If NextSheetName = BaseName & " (" & (Version + 1) & ")" Worksheets(NextSheetName).Visible = True Worksheets("family totals").Range("A5").EntireRow.Hidden = False End Sub "Roxy" wrote: Oops sorry I guess I wasn't clear enough, I have already created the 4 extra sheets. They are hidden until the user clicks the button. Is there a way to differentiate 'click number one' from 'click number two'? Right now I have changed my macro so that when a user clicks the button just once the "Extra Earned Income Meth 1" shows. But can the user go back click the same button again, and get my already created sheet "Extra Earned Income Meth 1 (2)" sheet to be visible, and so on for all the sheets? This is what I've got so far, but it doesn't do anything if you try a second click. I've renamed the Private Sub's even though there is only 1 CommandButton cause I kept getting an error...... Please let me know if I don't make any sense macros/codes are still new to me :) Private Sub CommandButton1_Click() Worksheets("Extra Earned Income Methd 1").Visible = True Sheets("Extra Earned Income Methd 1").Select Worksheets("Extra Earned Income Methd 1").Visible = True Worksheets("family totals").Range("A5").EntireRow.Hidden = False End Sub Private Sub CommandButton2_Click() Worksheets("Extra Earned Income Methd 1 (2)").Visible = True Sheets("Extra Earned Income Methd 1 (2)").Select Worksheets("Extra Earned Income Methd 1 (2)").Visible = True Worksheets("family totals").Range("A6").EntireRow.Hidden = False End Sub Private Sub CommandButton3_Click() Worksheets("Extra Earned Income Methd 1 (3)").Visible = True Sheets("Extra Earned Income Methd 1 (3)").Select Worksheets("Extra Earned Income Methd 1 (3)").Visible = True Worksheets("family totals").Range("A7").EntireRow.Hidden = False End Sub Private Sub CommandButton4_Click() Worksheets("Extra Earned Income Methd 1 (4)").Visible = True Sheets("Extra Earned Income Methd 1 (4)").Select Worksheets("Extra Earned Income Methd 1 (4)").Visible = True Worksheets("family totals").Range("A8").EntireRow.Hidden = False Sheets("Extra Earned Income Methd 1").Select End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Click Help
I tried this by deleting everything that I had and copying and pasting yours
in and I got a "Run time error '9' subscript out of range" and when I tried to debug it it highlighted the following part of the code in yellow: Worksheets(NextSheetName).Visible = True Not sure what to do next? All your help is greatly appreciated. I also tried another way of easeing this problem of mine yesterday by creating a Table of Contents with Hyperlinks, but the links wouldn't work after I hide the sheets I didn't want see/use until they had to. So I don't know the best way to solve my problem i need to do this to 5 other sheets so a grand total of 30 sheets :( Thanks again Roxy! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Click Help
The sheetname is not valid. Add a msgbox before the error to see the
problem. My code is working with the active sheet, you may need to make a slight change to pick a particul sheet. You can try to make one of the Extra Earned Income Methd 1 sheet active when running the code. msgbox(NextSheetName) Worksheets(NextSheetName).Visible = True "Roxy" wrote: I tried this by deleting everything that I had and copying and pasting yours in and I got a "Run time error '9' subscript out of range" and when I tried to debug it it highlighted the following part of the code in yellow: Worksheets(NextSheetName).Visible = True Not sure what to do next? All your help is greatly appreciated. I also tried another way of easeing this problem of mine yesterday by creating a Table of Contents with Hyperlinks, but the links wouldn't work after I hide the sheets I didn't want see/use until they had to. So I don't know the best way to solve my problem i need to do this to 5 other sheets so a grand total of 30 sheets :( Thanks again Roxy! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Button Click Help
Ok I got the msg box to work and was able to activate the proper sheet so now
the code works. But it still only will allow you to click to unhide 1 sheet. You can't go back and click the button again and get another sheet to unhide, the msg box just keeps refering to the same one. Any ideas or am I at a loss and need to just settle for having all of the hidden sheets pop up when the button is clicked? Thanks! This is the main sheet with the button: Earned Income Methd 1 Theses are the ones that are hidden off of it: Extra Earned Income Methd 1 Extra Earned Income Methd 1 (2) Extra Earned Income Methd 1 (3) Extra Earned Income Methd 1 (4) This is how I've changed the code so far: Private Sub CommandButton1_Click() ActiveWorkbook.Sheets("Extra Earned Income Methd 1").Activate SheetName = ActiveSheet.Name 'check if there is a parenthesis in sheet name 'No parenthesis then version 1 If InStr(SheetName, "(") = 0 Then BaseName = SheetName Version = 1 Else 'BaseName is sheet name left of parenthesis 'Use trim to remove space at end of name BaseName = Trim(Left(SheetName, InStr(SheetName, "(") - 1)) 'version is number after parenthis Version = Mid(SheetName, InStr(SheetName, "(") + 1) 'remove closing parenthisis Version = Val(Trim(Left(Version, _ InStr(Version, ")") - 1))) End If NextSheetName = BaseName & " (" & (Version + 1) & ")" MsgBox (NextSheetName) Worksheets(NextSheetName).Visible = True Worksheets("family totals").Range("A5").EntireRow.Hidden = False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command Button Click | Excel Discussion (Misc queries) | |||
vba to click a command button | Excel Programming | |||
VBA Click Command Button | Excel Programming | |||
why do i have to click my 2nd command button twice | Excel Programming | |||
How to add a command button and on-click within vba | Excel Programming |