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
|