MS Excel VBA, passing variable from sub to form

Question

How can I use my variable from sub procedure to add specific items for comboBox in userForm?

Sub procedure (Module code) -

Dim main As Integer

Public Sub dataValidation()
    For i = 3 To 22
        If Cells(i, 7).Value = "" Then
            If Cells(i, 6).Value = "x" Then
                main = 1
            ElseIf Cells(i, 6).Value = "y" Then
                main = 2
            ElseIf Cells(i, 6).Value = "z" Then
                main = 3
            End If
            form.Show
        End If
    Next
End Sub

Form initialization (Form code) -

Private Sub UserForm_Initialize()
    cboSubtype.Value = "Select subtype"
    if main = 1 then
        cboSubtype.AddItem "a"
        cboSubtype.AddItem "s"
    elseif main = 2 then
        cboSubtype.AddItem "d"
        cboSubtype.AddItem "f"
    elseif main = 3 then
        cboSubtype.AddItem "g"
        cboSubtype.AddItem "h"
End Sub

you need both Subs to recognixe the same main, so in your first line use Public main As Integer. You should learn how to use Select Case instaed of multiple If >> Else . — Shai Rado
– Shai Rado, Commented Jan 8, 2017 at 14:55
Ideally for passing values to a form and using the user's selection in your code, you create an instance of the form, pass the values to it and retrieve the selected value when the OK button of the form is pressed. This post of mine has a sample workbook you can download: yoursumbuddy.com/a-flexible-vba-chooser-form. The best explanation is in Professional Excel Development by Stephen Bullen, et. al. — Doug Glancy
– Doug Glancy, Commented Jan 8, 2017 at 16:32

Shai Rado · Accepted Answer · 2017-01-08 15:03:17Z

2

The code below is using the methods you wanted, to pass a variable main from module to User_Form init event.

Note: You could perform is all in 1 code (inside the user_form init event).

Sub dataValidation Code (Module)

Option Explicit

Public main As Integer

Public Sub dataValidation()

Dim i As Integer

For i = 3 To 22
    If Cells(i, 7).Value = "" Then
        Select Case Cells(i, 6).Value
            Case "s"
                main = 1

            Case "y"
                main = 2

            Case "z"
                main = 3

        End Select
        form.Show
    End If
Next i

End Sub

Sub User_Form Code (on init event)

Private Sub UserForm_Initialize()

With cboSubtype
    .Value = "Select subtype"

    Select Case main
        Case 1
            .AddItem "a"
            .AddItem "s"

        Case 2
            .AddItem "d"
            .AddItem "f"

        Case 3
            .AddItem "g"
            .AddItem "h"

    End Select
End With

End Sub

answered Jan 8, 2017 at 15:03

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

8 Comments

m aksem Over a year ago

It works. As far as i understood variable can be passed to form init event because of Select case statement, am I right?

Shai Rado Over a year ago

the value in your case is not passed, but it's a Public variable that all modules recognize. You can keep your If and Else but why would you ?

m aksem Over a year ago

Got it. There's no reason to stick with If else from now on =] I'm struggling for a while with a new issue, maybe you can help - How can I take value from SelectBox and place it in cell?

Shai Rado Over a year ago

with your code, since you have more than **2(( Ifs it's easier and makes more sense to use Select Case. In other scenarios, where you have 2 or less Ifs then it makes more sense keeping the If. Am I being clear now or made more of a mess ?

m aksem Over a year ago

It seems like you misunderstood my question, please, check last respond to this question.

|

Doug Coats · Accepted Answer · 2017-01-08 14:51:26Z

1

Private Sub UserForm_Initialize()
this = "this": that = "that": thenext = "thenext"
With ComboBox1
    .AddItem this
    .AddItem that
    .AddItem thenext
End With
End Sub

This compiles for me.

answered Jan 8, 2017 at 14:51

Doug Coats

7,2179 gold badges32 silver badges55 bronze badges

3 Comments

m aksem Over a year ago

On line 3 you add combo box to user form or select it to add items?

m aksem Over a year ago

One more question - when I use UserForm1.Show inside my module code it ignores code inside Private Sub UserForm_Initialize() and pop form without any combo box items.

Doug Coats Over a year ago

@maksem the general syntax for adding items to comboboxes/listboxes is with ".additem." I just use "With" because i think it looks cleaner.

Collectives™ on Stack Overflow

MS Excel VBA, passing variable from sub to form

2 Answers 2

8 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related