Stream Lining a long code

Question

I have below codes running through multiple command buttons. Just wanted to know if there is any method to stream line. Each button works in a flow having certain characteristics. I am sure there are ways to cutoff excess junk.

Private Sub CommandButton1_Click()
ActiveSheet.Unprotect "bir@2016"
 Range("A17").Select
    ActiveCell.FormulaR1C1 = "Research"
    Sheets("Questionnaire").Select
    Sheets("Questionnaire").Range("A1").Select
    Sheets("Analyst Score").Select
    Sheets("Questionnaire").Select
ActiveSheet.Protect "bir@2016"
End Sub

Private Sub CommandButton10_Click()
ActiveWorkbook.Unprotect "bir@2016"
Sheets("Investigation Comments Input").Visible = False
Sheets("Analyst Score").Visible = False
Sheets("Questionnaire").Select
ActiveWorkbook.Protect "bir@2016"
End Sub

Private Sub CommandButton11_Click()
ActiveWorkbook.Unprotect "bir@2016"
Sheets("Report Template").Visible = True
Sheets("Report Template").Select
Sheets("Report Template").Range("B4").Select
ActiveSheet.Protect "bir@2016"
End Sub

Private Sub Commandbutton2_Click()
ActiveSheet.Unprotect "bir@2016"
Range("A17").Select
    ActiveCell.FormulaR1C1 = "Quality Check"
    Sheets("Questionnaire").Select
    Sheets("Questionnaire").Range("A1").Select
    Sheets("Analyst Score").Select
    Sheets("Questionnaire").Select

Sheets("Questionnaire").Range("W1").Select
    ActiveCell.Value = Time
Sheets("Questionnaire").Range("A1").Select

ActiveSheet.Protect "bir@2016"
End Sub
Private Sub CommandButton3_Click()
ActiveSheet.Unprotect "bir@2016"
Range("A17,B17,B1,C1,B3:B5,B7,H19:I127,O19:O127,K19:K127,L19:L127").Select
    Range("H19").Activate
    Selection.ClearContents
Sheets("Questionnaire").Select
Sheets("Questionnaire").Range("W1:X1,Z1:AE1").Select
ActiveSheet.Unprotect "bir@2016"
Selection.ClearContents
Sheets("Analyst Score").Select
ActiveSheet.Protect "bir@2016"
End Sub
Private Sub CommandButton4_Click()
ActiveSheet.Unprotect "bir@2016"
ActiveSheet.Unprotect "bir@2016"
Range("I19:I127").Select
    Range("I19").Activate
    Selection.ClearContents
    Range("N7").Select
ActiveSheet.Protect "bir@2016"
End Sub
Private Sub CommandButton5_Click()
ActiveSheet.Unprotect "bir@2016"
ActiveWindow.ScrollRow = 9
Range("A19").Select
ActiveWindow.FreezePanes = True
ActiveSheet.Protect "bir@2016"
End Sub
Private Sub CommandButton6_Click()
ActiveSheet.Unprotect "bir@2016"
ActiveWindow.FreezePanes = False
    ActiveWindow.SmallScroll Down:=-33
ActiveSheet.Protect "bir@2016"
End Sub
Private Sub CommandButton7_Click()
ActiveSheet.Unprotect "bir@2016"
ActiveSheet.Unprotect "bir@2016"
Range("H19:H127").Select
    Range("H19").Activate
    Selection.ClearContents
    Range("N7").Select
ActiveSheet.Protect "bir@2016"
End Sub
Private Sub CommandButton8_Click()
ActiveSheet.Unprotect "bir@2016"
Rows("17:127").Select
    Selection.EntireRow.Hidden = True
    Range("H5").Select
Range("Z:Z,AA:AA,AB:AB").Select
    Range("AB9").Activate
    Selection.EntireColumn.Hidden = True
    Range("M14").Select
ActiveSheet.Protect "bir@2016"
End Sub
Private Sub CommandButton9_Click()
ActiveSheet.Unprotect "bir@2016"
Rows("17:127").Select
    Selection.EntireRow.Hidden = False
    Range("H5").Select
    Range("Z:Z,AA:AA,AB:AB").Select
    Range("AB9").Activate
    Selection.EntireColumn.Hidden = False
    Range("M14").Select
ActiveSheet.Protect "bir@2016"
End Sub
Private Sub DEWS_Click()
ActiveSheet.Unprotect "bir@2016"
 Range("A17").Select
    ActiveCell.FormulaR1C1 = "Dews"
    Sheets("Questionnaire").Select
Sheets("Questionnaire").Range("W1").Select
    ActiveSheet.Unprotect "bir@2016"
    ActiveCell.Value = Time
    Range("W2").Value = Date
Sheets("Questionnaire").Range("A1").Select
ActiveSheet.Protect "bir@2016"
End Sub
Private Sub Worksheet_Calculate()
If Range("E5").Value < 1 Then Me.Shapes("CommandButton2").Visible = False
If Range("E5").Value > 1 Then Me.Shapes("CommandButton2").Visible = True
End Sub

Get rid of all the `.Select` would be the first place to start. Try reading [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — Kyle, Oct 06 '16 at 18:20
if you code works then your question should be not posted here but in `http://codereview.stackexchange.com/` — user3598756, Oct 06 '16 at 18:28

score 0 · Answer 1 · answered Oct 06 '16 at 20:32

There a lot of ways to shorten your code:

1) Start off with Kyle's comment and reduce your select statements.

2) If you are looking to visually unclutter your code, make better use of white space.

3) In commandbutton4, commandbutton7, you unprotect the same sheet twice.

Other than the above, there is not much more to do if you need every single one of these buttons. Are you sure you can't combine buttons?

Stream Lining a long code

1 Answers1