How’s going? I’am a beginner in VBA and I have been try to figure it out how to do it the following question, but I just can’t. So, I will explain my struggle in VBA and hopefully you guys can have a solution. I have a userform with 3 Opttion Buttons. I have a code for each Opption Button and they are pretty similar. In my workflow, I need to duplicate the userforms, cause, basically, I will use the same structure everytime, but in different cells. I will post a pic so you guys can understand better. The following pic is the code of one of the opption buttons:
So far, so good, the code works just as exactly is supposed to be. But, since I need do duplicate the UserForms, cause I will use the same strutucture in others cells, it will be easier if I have some general declaration that will be applied in all the opptions buttons that my Userform has, working like a “global variable” for all the opptions buttons that my userform has. So, what I was trying to do is:
When I declare general variables like the pic above, the code crashs to recognize “set Rng1 = Range(“AK5”)”. So, you guys know how to solve this problem? I guess it’s not that complicate, I’m just not knowing how to declare general declarations in Userform. I will post the codes of each opption button below:
**OptionButton1:**
'Where it's going to copy
Dim Rng1 As Range
Set Rng1 = Range("AK5")
'Where it's going to past
Dim Rng2 As Range
Set Rng2 = Range("H24")
'Name Userform
Dim myUF As Object
Set myUF = zzzz22
Application.ScreenUpdating = False
Rng1.Select
Selection.Copy
Rng2.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True
Unload myUF
ActiveSheet.Shapes.Range(Array("Rectangle 2")).Select
**OptionButton2:**
'Where it's going to copy
Dim Rng1 As Range
Set Rng1 = Range("AK5")
'Where it's going to past
Dim Rng2 As Range
Set Rng2 = Range("H24")
'Name Userform
Dim myUF As Object
Set myUF = zzzz22
Application.ScreenUpdating = False
Rng1.Offset(1, 0).Select
Selection.Copy
Rng2.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True
Unload myUF
ActiveSheet.Shapes.Range(Array("Rectangle 2")).Select
**OptionButton 3:**
'Where it's going to copy
Dim Rng1 As Range
Set Rng1 = Range("AK5")
'Where it's going to past
Dim Rng2 As Range
Set Rng2 = Range("H24")
'Name Userform
Dim myUF As Object
Set myUF = zzzz22
Application.ScreenUpdating = False
Rng1.Offset(2, 0).Select
Selection.Copy
Rng2.Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A1").Select
Application.ScreenUpdating = True
Unload myUF
ActiveSheet.Shapes.Range(Array("Rectangle 2")).Select
The way I have my workflow, everytime I duplicate a new userform, I will have to change the value of the cells for each opption button. If I manage to have a general declaration that will be applied for the three opptions buttons, my workflow will be much faster, since I will only have to change the cells of the general declaration, and that will be applied as well for each option button all at once. Hope I made myself clear, if you guys have a question about something, I will be answering. Thanks for your attention!!
It could be structured like this, using constants for the range addresses:
Const RNG_1 As String = "AK5"
Const RNG_2 As String = "H24"
Private Sub OptionButton1_Click()
Application.ScreenUpdating = False
With ActiveSheet
.Range(RNG_1).Copy .Range(RNG_2)
.Shapes("Rectangle 2").Select
End With
Unload Me
End Sub