CODEWITHSUNDEEP
excelvba
m aksem
m aksem

Reputation: 571

MS Excel VBA, passing variable from sub to form

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

Upvotes: 0

Views: 2517

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

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

Upvotes: 2

Doug Coats
Doug Coats

Reputation: 7117

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.

Upvotes: 1

Related Questions