Command Button Click Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roxy - 29 May 2008 17:22 GMT

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

Reply to this Message

Joel - 29 May 2008 17:33 GMT

Private Sub CommandButton1_Click()
Worksheets("Extra Earned Income Methd 1").Copy _
after:=Worksheets("Extra Earned Income Methd 1")
End Sub

> 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
[quoted text clipped - 37 lines]
>
> End Sub

Reply to this Message

Roxy - 29 May 2008 17:55 GMT

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

Reply to this Message

Joel - 29 May 2008 18:27 GMT

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.

> 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

Reply to this Message

Roxy - 29 May 2008 22:58 GMT

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

Reply to this Message

Joel - 30 May 2008 04:20 GMT

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

> 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.
[quoted text clipped - 38 lines]
>
> End Sub

Reply to this Message

Roxy - 30 May 2008 17:35 GMT

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!

Reply to this Message

Joel - 30 May 2008 17:47 GMT

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

> 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
[quoted text clipped - 10 lines]
>
> Thanks again Roxy!

Reply to this Message

Roxy - 30 May 2008 22:33 GMT

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 to this Message