How can I reduce repetative code

[leerem]

I have set up a userform with all the weeks of the year as optionbuttons to
save data too, with a command button to activate depending on which
optionbutton has been made true. My question: Instead of having to repeat the
same code over and over again stating open new workbook / name workbook /
copy data to workbook / save data / close workbook. a good 40 lines of code
for each option button. Is there away around this?

 

[Mike H]

Hi,

Pass the name of the button that was pressed to a single routine

Private Sub CommandButton1_Click()
ButtonPressed = CommandButton1.Caption
MyRoutine (ButtonPressed)
End Sub

Mike

 

[leerem]

Hi Mike, I'm fairly new to this, could you please go into a bit more detail

 

[Bob Phillips]

Use called procedures

Private Sub OptionButton1_Click()

Call myProc
End Sub

Private Sub OptionButton2_Click()

Call myProc
End Sub

Private Function myProc()

Workbooks.Open ... 'etc
'other code
End Sub

If each button opens a different workbook, add an argument


Private Sub OptionButton1_Click()

Call myProc("C:\test\Book1.xls")
End Sub

Private Sub OptionButton2_Click()

Call myProc("C:\test\Book2.xls")
End Sub

Private Function myProc(ByVal Filename As String)

Workbooks.Open Filename
'other code
End Sub

 

[Mike H]

Hi,

It's difficult to provide too much help because you don't go into detail
about your code but the example I gave you would pass the caption of the
button that had been pressed to to a routine called "MyRoutine". You could
equally easily pass other parameters you wanted to your code depending on
which button had called it and could do this for multiple buttons.

The example give your other response shows how you could pass a workbook name.

Mike