Multiple buttons Hide/unhide on a locked sheet

G

gpatel_here

New Member
Joined
Jan 13, 2015
Messages
11
I want to hide/unhide button in a sequence First button is default unhide from the second button I am unable to click on the second button to hide it and unhide the third button.

Here is the code, anyone please suggest where I am wrong.

VBA Code: 
Sub Rev_1()
'Add Revision 1
ActiveSheet.Unprotect Password:=Pwd
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim UserEntry, UserEntry1, UserValue1 As String
Dim Msg, Msg1 As String
Dim UserValue As Date
Msg = "Enter Date as dd/mm/yyyy"
Msg1 = "Enter Brief Reason and details of Revision."

Sheets("PO").Copy , Sheets(Sheets.Count)
ActiveSheet.Name = "PO_rev_1"

Sheets("Data").Activate
Sheets("Data").Range("C7").Value = "1"
'Sheets("Data").Range("E7:I7").Locked = False
   
UserEntry = InputBox(Msg) ' Revision Date
If UserEntry = "" Then GoTo getout
    If IsDate(UserEntry) Then
        UserValue = CDate(UserEntry)
        If UserValue >= ActiveSheet.Range("E4").Value Then
            ActiveSheet.Range("D7").Value = UserValue
        Else
            MsgBox "Date Error Please verify the Date."
            UserEntry = InputBox(Msg)
        End If
    Else
        MsgBox "Please try again.  Enter date as dd/mm/yyyy"
        GoTo getout
    End If

UserEntry1 = InputBox(Msg1) ' Revision Details
If UserEntry1 = "" Then
    MsgBox "Error Please enter revision details."
    UserEntry1 = InputBox(Msg1)
Else
    UserValue1 = (UserEntry1)
    ActiveSheet.Range("E7:J7").Value = UserValue1
End If

Sheets("Data").Range("L41:R41").Copy
Sheets("PO_rev_1").Activate
Range("E66:U66").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("U2").Value = "/r1"
Application.CutCopyMode = False

ActiveSheet.Unprotect Password:=Pwd
Range("T4:U4").Value = Sheets("Data").Range("D7").Value
'ActiveSheet.Buttons.Delete
ActiveSheet.Protect Password:=Pwd

Sheets("Data").Activate
Range("C2").Select

Sheets("Data").Buttons("Rev_1").Visible = False
Sheets("Data").Buttons("Rev_2").Visible = True
Sheets("Data").Buttons("Rev_3").Visible = False
Sheets("Data").Buttons("Rev_4").Visible = False
Sheets("Data").Buttons("Rev_5").Visible = False


getout:
Application.EnableEvents = True
ActiveSheet.Protect Password:=Pwd
Application.ScreenUpdating = True
End Sub


Sub Rev_2()
'Add Revision 2
ActiveSheet.Unprotect Password:=Pwd
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim UserEntry, UserEntry1, UserValue1 As String
Dim Msg, Msg1 As String
Dim UserValue As Date
Msg = "Enter Date as dd/mm/yyyy"
Msg1 = "Enter Brief Reason and details of Revision."

Sheets("PO_rev_1").Copy , Sheets(Sheets.Count)
ActiveSheet.Name = "PO_rev_2"

Sheets("Data").Activate
Sheets("Data").Range("C8").Value = "2"
'Sheets("Data").Range("E8:I8").Locked = False

UserEntry = InputBox(Msg) ' Revision Date
If UserEntry = "" Then GoTo getout
    If IsDate(UserEntry) Then
        UserValue = CDate(UserEntry)
        If UserValue >= ActiveSheet.Range("E4").Value Then
            ActiveSheet.Range("D8").Value = UserValue
        Else
            MsgBox "Date Error Please verify the Date."
            UserEntry = InputBox(Msg)
        End If
    Else
        MsgBox "Please try again.  Enter date as dd/mm/yyyy"
        GoTo getout
    End If

UserEntry1 = InputBox(Msg1) ' Revision Details
If UserEntry1 = "" Then
    MsgBox "Error Please enter revision details."
    UserEntry1 = InputBox(Msg1)
Else
    UserValue1 = (UserEntry1)
    ActiveSheet.Range("E8:J8").Value = UserValue1
End If

Sheets("Data").Range("L42:R42").Copy
Sheets("PO_rev_2").Activate
Range("E66:U66").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("U2").Value = "/r2"
Application.CutCopyMode = False

ActiveSheet.Unprotect Password:=Pwd
Range("T4:U4").Value = Sheets("Data").Range("D8").Value
'ActiveSheet.Buttons.Delete
ActiveSheet.Protect Password:=Pwd

Sheets("Data").Activate
Range("C2").Select

Sheets("Data").Buttons("Rev_1").Visible = False
Sheets("Data").Buttons("Rev_2").Visible = False
Sheets("Data").Buttons("Rev_3").Visible = True
Sheets("Data").Buttons("Rev_4").Visible = False
Sheets("Data").Buttons("Rev_5").Visible = False


getout:
Application.EnableEvents = True
ActiveSheet.Protect Password:=Pwd
Application.ScreenUpdating = True
End Sub

Sub Rev_3()
'Add Revision 3
ActiveSheet.Unprotect Password:=Pwd
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim UserEntry, UserEntry1, UserValue1 As String
Dim Msg, Msg1 As String
Dim UserValue As Date
Msg = "Enter Date as dd/mm/yyyy"
Msg1 = "Enter Brief Reason and details of Revision."

Sheets("PO_rev_2").Copy , Sheets(Sheets.Count)
ActiveSheet.Name = "PO_rev_3"

Sheets("Data").Activate
Sheets("Data").Range("C9").Value = "3"
'Sheets("Data").Range("E9:I9").Locked = False

UserEntry = InputBox(Msg) ' Revision Date
If UserEntry = "" Then GoTo getout
    If IsDate(UserEntry) Then
        UserValue = CDate(UserEntry)
        If UserValue >= ActiveSheet.Range("E4").Value Then
            ActiveSheet.Range("D9").Value = UserValue
        Else
            MsgBox "Date Error Please verify the Date."
            UserEntry = InputBox(Msg)
        End If
    Else
        MsgBox "Please try again.  Enter date as dd/mm/yyyy"
        GoTo getout
    End If

UserEntry1 = InputBox(Msg1) ' Revision Details
If UserEntry1 = "" Then
    MsgBox "Error Please enter revision details."
    UserEntry1 = InputBox(Msg1)
Else
    UserValue1 = (UserEntry1)
    ActiveSheet.Range("E9:J9").Value = UserValue1
End If

Sheets("Data").Range("L43:R43").Copy
Sheets("PO_rev_3").Activate
Range("E66:U66").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("U2").Value = "/r3"
Application.CutCopyMode = False

ActiveSheet.Unprotect Password:=Pwd
Range("T4:U4").Value = Sheets("Data").Range("D9").Value
'ActiveSheet.Buttons.Delete
ActiveSheet.Protect Password:=Pwd

Sheets("Data").Activate
Range("C2").Select

Sheets("Data").Buttons("Rev_1").Visible = False
Sheets("Data").Buttons("Rev_2").Visible = False
Sheets("Data").Buttons("Rev_3").Visible = False
Sheets("Data").Buttons("Rev_4").Visible = True
Sheets("Data").Buttons("Rev_5").Visible = False


getout:
Application.EnableEvents = True
ActiveSheet.Protect Password:=Pwd
Application.ScreenUpdating = True
End Sub

Sub Rev_4()
'Add Revision 4
ActiveSheet.Unprotect Password:=Pwd
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim UserEntry, UserEntry1, UserValue1 As String
Dim Msg, Msg1 As String
Dim UserValue As Date
Msg = "Enter Date as dd/mm/yyyy"
Msg1 = "Enter Brief Reason and details of Revision."

Sheets("PO_rev_3").Copy , Sheets(Sheets.Count)
ActiveSheet.Name = "PO_rev_4"

Sheets("Data").Activate
Sheets("Data").Range("C10").Value = "4"
'Sheets("Data").Range("E10:I10").Locked = False

UserEntry = InputBox(Msg) ' Revision Date
If UserEntry = "" Then GoTo getout
    If IsDate(UserEntry) Then
        UserValue = CDate(UserEntry)
        If UserValue >= ActiveSheet.Range("E4").Value Then
            ActiveSheet.Range("D10").Value = UserValue
        Else
            MsgBox "Date Error Please verify the Date."
            UserEntry = InputBox(Msg)
        End If
    Else
        MsgBox "Please try again.  Enter date as dd/mm/yyyy"
        GoTo getout
    End If

UserEntry1 = InputBox(Msg1) ' Revision Details
If UserEntry1 = "" Then
    MsgBox "Error Please enter revision details."
    UserEntry1 = InputBox(Msg1)
Else
    UserValue1 = (UserEntry1)
    ActiveSheet.Range("E10:J10").Value = UserValue1
End If

Sheets("Data").Range("L44:R44").Copy
Sheets("PO_rev_4").Activate
Range("E66:U66").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("U2").Value = "/r4"
Application.CutCopyMode = False

ActiveSheet.Unprotect Password:=Pwd
Range("T4:U4").Value = Sheets("Data").Range("D10").Value
'ActiveSheet.Buttons.Delete
ActiveSheet.Protect Password:=Pwd

Sheets("Data").Activate
Range("C2").Select

Sheets("Data").Buttons("Rev_1").Visible = False
Sheets("Data").Buttons("Rev_2").Visible = False
Sheets("Data").Buttons("Rev_3").Visible = False
Sheets("Data").Buttons("Rev_4").Visible = False
Sheets("Data").Buttons("Rev_5").Visible = True


getout:
Application.EnableEvents = True
ActiveSheet.Protect Password:=Pwd
Application.ScreenUpdating = True
End Sub


Sub Rev_5()
'Add Revision 5
ActiveSheet.Unprotect Password:=Pwd
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim UserEntry, UserEntry1, UserValue1 As String
Dim Msg, Msg1 As String
Dim UserValue As Date
Msg = "Enter Date as dd/mm/yyyy"
Msg1 = "Enter Brief Reason and details of Revision."

Sheets("PO_rev_4").Copy , Sheets(Sheets.Count)
ActiveSheet.Name = "PO_rev_5"

Sheets("Data").Activate
Sheets("Data").Range("C11").Value = "5"
'Sheets("Data").Range("E11:I11").Locked = False

UserEntry = InputBox(Msg) ' Revision Date
If UserEntry = "" Then GoTo getout
    If IsDate(UserEntry) Then
        UserValue = CDate(UserEntry)
        If UserValue >= ActiveSheet.Range("E4").Value Then
            ActiveSheet.Range("D11").Value = UserValue
        Else
            MsgBox "Date Error Please verify the Date."
            UserEntry = InputBox(Msg)
        End If
    Else
        MsgBox "Please try again.  Enter date as dd/mm/yyyy"
        GoTo getout
    End If

UserEntry1 = InputBox(Msg1) ' Revision Details
If UserEntry1 = "" Then
    MsgBox "Error Please enter revision details."
    UserEntry1 = InputBox(Msg1)
Else
    UserValue1 = (UserEntry1)
    ActiveSheet.Range("E11:J11").Value = UserValue1
End If

Sheets("Data").Range("L45:R45").Copy
Sheets("PO_rev_5").Activate
Range("E66:U66").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("U2").Value = "/r5"
Application.CutCopyMode = False

ActiveSheet.Unprotect Password:=Pwd
Range("T4:U4").Value = Sheets("Data").Range("D11").Value
'ActiveSheet.Buttons.Delete
ActiveSheet.Protect Password:=Pwd

Sheets("Data").Activate
Range("C2").Select

Sheets("Data").Buttons("Rev_1").Visible = False
Sheets("Data").Buttons("Rev_2").Visible = False
Sheets("Data").Buttons("Rev_3").Visible = False
Sheets("Data").Buttons("Rev_4").Visible = False
Sheets("Data").Buttons("Rev_5").Visible = False

getout:
Application.EnableEvents = True
ActiveSheet.Protect Password:=Pwd
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Nevermind I figured it out. My buttons name had changed somehow. All is OK.

I am not deleting the post since this logic can be useful to somone
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Locking a row after info entered
Replies
1
Views
450
RustamMika
R
S
  • Solved
VBA: Am I double-calling a Sub given nested If statements? Or other inefficiencies?
Replies
8
Views
238
dreid1011
dreid1011
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
263
Gajendran Yadhav
G
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
384
AndyEd
A
S
  • Solved
Alternative option for a data validation list to a drop down function on an existing sheet with my workbook?
Replies
7
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,214,651
Messages
6,120,744
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top