excelvbavariablesbuttonuserform

General declaration in UserForm, Excel


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:

First pic

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:

Second pic

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!!


Solution

  • 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