Referencing VB userforms from Excel cell

Question

Referencing VB userforms from Excel cell

cjg666 (Civil/Environmental)

(OP)

29 Feb 04 01:51

Hey guys,

I'm trying to write some simple code that will allow the userform that's opened by a single method to be altered depending on the value in an excel cell.
In the base case, cell A1 in Sheet1 has the value "Userform1". With in the method code I now need to transform this cell reference into a format that is compatible with the "Show" method.

Any help would be welcomed with open arms.

Craig

rodesh (Computer) · Answer 1 · 2004-03-02 19:39:59

The main thing is That Userform1 of VBA refer to "Object" whereas Userform1 of cell A1 refere to "string" value so these are not comparable.The following is the simple code which would work for you.Assume you have three userforms.

Sub Excelform()
    Select Case Cells(1, 1)
        Case "userform1"
            UserForm1.Show
        Case "userform2"
            UserForm2.Show
        Case "userform3"
            UserForm3.Show
        End Select
End Sub

shakildor (Civil/Environmental) · Answer 2 · 2004-04-06 00:46:31

Hey cjg666!!!!
i hope this will solve ur problem
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 1 Then
    Select Case Target
        Case "userform1"
            UserForm1.Show
        Case "userform2"
            UserForm2.Show
        Case "userform3"
            UserForm3.Show
        End Select
    Else
        UserForm1.Hide
        UserForm2.Hide
        UserForm3.Hide
    End If
End Sub

shakildor
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 1 Then
    Select Case Target
        Case "userform1"
            UserForm1.Show
        Case "userform2"
            UserForm2.Show
        Case "userform3"
            UserForm3.Show
        End Select
    Else
        UserForm1.Hide
        UserForm2.Hide
        UserForm3.Hide
    End If
End Sub

Come Join Us!

Posting Guidelines

Referencing VB userforms from Excel cell