Is there a tidier way to write this code?

[CarrieAWalton]

Hi all,

I'm updating a time in motion sheet at work. I have a worksheet full of command buttons which all input information into worksheets named "1st" through to "31st". These worksheets need to be password protected so that users can SEE the info contained within them, but not be able to EDIT the sheets (otherwise people fiddle their figures), so I'm adding code to unprotect the sheet, input the data, then protect it again.

However, my current code is messy (I don't fully understand VBA and tend to just bumble my way through) and I was hoping you lovely folks might be able to help me tidy it up. Here's a section of the code from one of my command buttons. I have eighteen to add the password unprotect/protect code to.

I hope you can help

Private Sub CommandButton6_Click()
'Call-Inbound
If Range("A2") = "1" Then
ans = InputBox("Case reference:", "Input")
Sheets("1st").Unprotect Password:="TIM"
Sheets("1st").Range("A" & Sheets("1st").Range("A" & Rows.Count).End(xlUp).Row + 1).Value = ans
Sheets("1st").Protect Password:="TIM"
    End If
    If Range("A2") = "2" Then
ans = InputBox("Case reference:", "Input")
Sheets("2nd").Unprotect Password:="TIM"
Sheets("2nd").Range("A" & Sheets("2nd").Range("A" & Rows.Count).End(xlUp).Row + 1).Value = ans
Sheets("2nd").Protect Password:="TIM"
    End If
    If Range("A2") = "3" Then
ans = InputBox("Case reference:", "Input")
Sheets("3rd").Unprotect Password:="TIM"
Sheets("3rd").Range("A" & Sheets("3rd").Range("A" & Rows.Count).End(xlUp).Row + 1).Value = ans
Sheets("3rd").Protect Password:="TIM"
    End If
    If Range("A2") = "4" Then
ans = InputBox("Case reference:", "Input")
Sheets("4th").Unprotect Password:="TIM"
Sheets("4th").Range("A" & Sheets("4th").Range("A" & Rows.Count).End(xlUp).Row + 1).Value = ans
    End If
End Sub

 

[CarrieAWalton]

Or indeed is there any way of saying to Excel "any time a command button is clicked, unprotect any input sheet using password xx then protect again using password xx"

Hmm. Whilst that sounds easier, it sounds like it wouldn't be possible...

 

[RoryA]

Do all the buttons have similar code? If so, what differs between them?

You could certainly refactor that code like this:

Private Sub CommandButton6_Click()
'Call-Inbound
If Range("A2") = "1" Then
AddToSheet Sheets("1st")
ElseIf Range("A2") = "2" Then
AddToSheet Sheets("2nd")
elseIf Range("A2") = "3" Then
AddToSheet Sheets("3rd")
elseIf Range("A2") = "4" Then
AddToSheet Sheets("4th")
    End If
End Sub

Sub AddToSheet(ws as worksheet)
ans = InputBox("Case reference:", "Input")
with ws
   .Unprotect Password:="TIM"
   .Range("A" & .Rows.Count).End(xlUp).Offset(1).Value = ans
   .Protect Password:="TIM"
End With
End Sub

 

[CarrieAWalton]

Hi, wow thanks for the quick reply Rory!

The code for 16 of the buttons is exactly the same, they just add the data to different columns in the worksheets.

So in the Sub Addtosheet you've mentioned above, how would I distinguish which button added to which column?

 

[RoryA]

You could add an additional column letter argument like this:

Private Sub CommandButton6_Click()
'Call-Inbound
If Range("A2") = "1" Then
AddToSheet Sheets("1st"), "A"
ElseIf Range("A2") = "2" Then
AddToSheet Sheets("2nd"), "A"
elseIf Range("A2") = "3" Then
AddToSheet Sheets("3rd"), "A"
elseIf Range("A2") = "4" Then
AddToSheet Sheets("4th"), "A"
    End If
End Sub

Sub AddToSheet(ws as worksheet, sColumn as String)
ans = InputBox("Case reference:", "Input")
with ws
   .Unprotect Password:="TIM"
   .Range(sColumn & .Rows.Count).End(xlUp).Offset(1).Value = ans
   .Protect Password:="TIM"
End With
End Sub

so you can call Addtosheet from all the buttons. Assuming the logic to select the sheet name is the same for all, you might make that into a function to return the relevant sheet name too.

 

[CarrieAWalton]

Rory I owe you a very large drink

Not only has that neatened it up, it's made it a lot quicker to alter should anything need changing. Thanks so very VERY much xx

 

[RoryA]

Glad to help. I like gin...