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
– Brian M Stafford, Commented Mar 28, 2019 at 15:19

cyboashu · Accepted Answer · 2019-03-28 15:30:54Z

4

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

answered Mar 28, 2019 at 15:30

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

T.M. Over a year ago

FYI Further information regarding modeless Userforms can be found at SO question Destroy a modeless Userform instance properly; c.f. also vitoshacademy.com/vba-the-perfect-userform-in-vba using classes :-)

GingerBoy Over a year ago

@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.

Mistella · Accepted Answer · 2019-03-28 15:34:04Z

1

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

answered Mar 28, 2019 at 15:34

Mistella

1,7482 gold badges13 silver badges20 bronze badges

1 Comment

GingerBoy Over a year ago

Thanks @Mistella, this was just what I was looking for.

Peicong Chen · Accepted Answer · 2019-03-28 15:34:36Z

1

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

answered Mar 28, 2019 at 15:34

Peicong Chen

3172 silver badges5 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related