Excel VBA Get value (Integer K) found in "UserForm1" Private Sub script, over to the "Module1" Sub Script

Question

I have trouble getting my value K (Integer) from my Private Sub in the UserForm1 over to my Sub in Module1. I Use the UserForm1 to declare what sheet I want to run code on (by choosing from a dropdown list that pops up, and then declare that as a value/Integer between 2 and 9), so I only need to transfer the value K I find in the UserForm1 Private Sub over to the Sub in my Module1.

I hope that was understandable, i.e. I want to be able to read the value K found in the UserForm1, in my Module1 script.

My current code is as follows, Beginning with the Module part:

Sub HenteMengderFraAutoCAD()
Dim K As Integer

Load UserForm1
UserForm1.Show

MsgBox (K)

Unload UserForm1
End Sub

Next is my code in the UserForm where I find the value to use in the code :

Private Sub UserForm_Activate()
ComboBox1.Clear

With ComboBox1
    .AddItem "M350 og XT"
    .AddItem "STB 300+450"
    .AddItem "Alufix"
    .AddItem "MevaDec og MevaFlex"
    .AddItem "Alshor Plus"
    .AddItem "Rapidshor"
    .AddItem "KLK og Sjaktdragere"
End With
End Sub

Private Sub CommandButton1_Click()
If ComboBox1 = "M350 og XT" Then
    K = 2
ElseIf ComboBox1 = "STB 300+450" Then
    K = 3
ElseIf ComboBox1 = "Alufix" Then
    K = 4
ElseIf ComboBox1 = "MevaDec og MevaFlex" Then
    K = 5
ElseIf ComboBox1 = "Alshor Plus" Then
    K = 6
ElseIf ComboBox1 = "Rapidshor" Then
    K = 7
ElseIf ComboBox1 = "KLK og Sjaktdragere" Then
    K = 9
End If
MsgBox (K)
UserForm1.Hide
End Sub

Private Sub CommandButton2_Click()
Unload UserForm1
End Sub

Actual result would be that MsgBox(K) in the Module1 script would show the same number that MsgBox(K) show me in the UserForm1. Now I get the right value for K (2 to 9 depending on what i choose in drop down list) in the MsgBox in the UserForm1, but in the Module1 MsgBox I only get 0.

In advance, thanks.

If `K` is declared `Public`, then the following would work: `MsgBox UserForm1.K`. — Brian M Stafford, Mar 28 '19 at 15:19

score 4 · Answer 1 · answered Mar 28 '19 at 15:30

UserForms are objects. Recommended and robust approach to read/write values from objects is using Properties. You can create a property and then access it in your module

Example Code. Read code comments for details.

User Form:

Option Explicit

'/ backing field for the custom property
Private m_MyProperty                As Long

'/ A public variable. Not recommended.
Public lAccessibleVariable          As Long

'/ Define property setters and getters
Public Property Let MyProperty(val As Long)
    m_MyProperty = val
End Property

Public Property Get MyProperty() As Long
 MyProperty = m_MyProperty
End Property

Private Sub CommandButton1_Click()
    '/ Do something to the property
    MyProperty = 10
    lAccessibleVariable = 100

    '/ Make sure you just hide the form and not close(destroy it)
    Me.Hide
End Sub

Module

Sub test()

    '/ Create an instance of the user form
    Dim frm As New UserForm1
    Dim lValFromUserForm  As Long

    '/ launch the user form
    frm.Show


    '/ Read back the property value
    lValFromUserForm = frm.MyProperty

    '/ do something with the returned value
    MsgBox lValFromUserForm

    '/Just for example, access the public variable.
    MsgBox frm.lAccessibleVariable

    '/ Now that you are done, destroy the user form
    Unload frm


End Sub

FYI Further information regarding modeless Userforms can be found at SO question [Destroy a modeless Userform instance properly](https://stackoverflow.com/questions/47357708/vba-destroy-a-modeless-userform-instance-properly); c.f. also https://www.vitoshacademy.com/vba-the-perfect-userform-in-vba/ using classes :-) — T.M., Mar 28 '19 at 20:25
@cyboashu Thank you for the thorough example! Very useful for later development and understanding. **I have one question tho:** Why Isn't it recommended to use Public varibles? In my whole code I have other `Sub` that also use the vaiable `K`. Could this be a problem? The variable `K` is defined in each og my `Sub` codes. @T.M. Thanks for the addional information. I will go through thisto learn more. — GingerBoy, Mar 29 '19 at 08:48

score 1 · Accepted Answer · answered Mar 28 '19 at 15:34

If, in the userform code, you change the inner references of UserForm1 to Me, i.e.

UserForm1.Hide
End Sub

Private Sub CommandButton2_Click()
Unload UserForm1

to

Me.Hide
End Sub

Private Sub CommandButton2_Click()
Unload Me

and declare a public variable in the userform like:

Public K As Integer

Then you can use:

Sub HenteMengderFraAutoCAD()
Dim K As Integer

With New UserForm1
    .Show
    K = .K
End With

MsgBox (K)
End Sub

Complete UserForm Code

Option Explicit
Public K As Integer

Private Sub UserForm_Activate()
ComboBox1.Clear

With ComboBox1
    .AddItem "M350 og XT"
    .AddItem "STB 300+450"
    .AddItem "Alufix"
    .AddItem "MevaDec og MevaFlex"
    .AddItem "Alshor Plus"
    .AddItem "Rapidshor"
    .AddItem "KLK og Sjaktdragere"
End With
End Sub

Private Sub CommandButton1_Click()
If ComboBox1 = "M350 og XT" Then
    K = 2
ElseIf ComboBox1 = "STB 300+450" Then
    K = 3
ElseIf ComboBox1 = "Alufix" Then
    K = 4
ElseIf ComboBox1 = "MevaDec og MevaFlex" Then
    K = 5
ElseIf ComboBox1 = "Alshor Plus" Then
    K = 6
ElseIf ComboBox1 = "Rapidshor" Then
    K = 7
ElseIf ComboBox1 = "KLK og Sjaktdragere" Then
    K = 9
End If
MsgBox (K)
Me.Hide
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Thanks @Mistella, this was just what I was looking for. – GingerBoy Mar 29 '19 at 08:32 — GingerBoy, Mar 29 '19 at 08:32

score 1 · Answer 3 · answered Mar 28 '19 at 15:34

My approach agrees wtih Brian M Stafford.

1st:declare K as public variable under your UserForm1 before any subroutines,

public K as integer

2nd:

Sub HenteMengderFraAutoCAD()

Load UserForm1
UserForm1.Show

MsgBox (UserForm1.K)

Unload UserForm1
End Sub

Excel VBA Get value (Integer K) found in "UserForm1" Private Sub script, over to the "Module1" Sub Script

3 Answers3