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.

Answer 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