200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
the code will copying amount from textbox to specific column based on selected one of the optionbuttons .
if I select optionbutton1 and fill amount in textbox 63 , then copy to column E and if i select optionbutton2 and fill amount in textbox63 then copy to column D . currently the code will just copy to VOUCHER sheet.
now I add new sheets , the combobox1 contains sheet names , so I want implementing the code to specific sheet based on matching with combobox1.
thanks
Alaa
In the attached revised file, I made your command buttom green and added the caption "Add to sheet" then modified the code (as in bold, with comments to help):
Private Sub CommandButton7_Click()
Dim ws As Worksheet
Dim myCol$
Dim lr As Long
' check an option was picked
If OptionButton7.Value = False And OptionButton8.Value = False Then MsgBox "Please pick option PAID or RECEIVED"
If OptionButton7.Value = True Then
'Set ws = Sheets("voucher")
' choose the sheet from the ComboBox selection
Set ws = Sheets(ComboBox1.Text)
myCol = "E"
ElseIf OptionButton8.Value = True Then
'Set ws = Sheets("voucher")
Set ws = Sheets(ComboBox1.Text)
myCol = "D"
End IfIt should now add the amount to the right sheet/ column but the forms "locks" so I changed your Activate code to remove the loop:
Private Sub UserForm_Activate()
'Do
Label63.Caption = Date & " " & Format(Now, "hh:mm:ss")
' DoEvents
' Loop Until ExitLoop
End SubI aslo added code to close the Userform once a voucher had been added to a sheet, to avoid confusion, by ending Private Sub CommandButton7_Click with:
' close the form
Unload Me
End Sub(You could instead reset the values in the UserForm if you want to)
Hope I got this right (the language used in your question is a bit confusing, presumably translated). If this fixes your problem, please remember to mark this Answer as Selected.