jeudi 28 mai 2015

Can someone help me simplfy my VBA code please

Hi i've got a dashboard that uses a userform box fileld with checkboxes to choose what data to display on a chart. At the minute my code is very copy and pasted. See below:

Private Sub CommandButton21_Click()
UserForm1.Show
If Worksheets("Data Directorate").Range("X4").Value = True Then UserForm1.CheckBox1 = True
If Worksheets("Data Directorate").Range("X5").Value = True Then UserForm1.CheckBox2 = True
If Worksheets("Data Directorate").Range("X6").Value = True Then UserForm1.CheckBox3 = True

Is there a way to use a loop to do this?

I've also got more repeated code later on:

Private Sub CheckBox1_Click()
  Select Case CheckBox1.Value
  Case True
  Worksheets("Data Directorate").Range("X4").Value = True
  Case False
  Worksheets("Data Directorate").Range("X4").Value = False
End Select
End Sub

This repeats for 24 check boxes. Again would it be possible to loop this? Thanks!




Aucun commentaire:

Enregistrer un commentaire