Creating Userforms in Excel

[Pianoman23]

Hi Guys
I'm familiar with creating UserForms using Excel VBA, but is it possible to have several command buttons access the same UserForm layout with variables for row and column dependant on the command button pressed?
I have a worksheet that contains all the information on several stores. My first worksheet is just a series of command buttons (1 to 36). What I want to do is press one of the command buttons and for the UserForm to display the information for that particular store.
Can anyone help?

 

[GTO]

...I have a worksheet that contains all the information on several stores...

...What I want to do is press one of the command buttons and for the UserForm to display the information for that particular store...

If you truly have only one worksheet with details for multiple stores, you might want to share how we are figuring out where an individual store's details reside in the sheet. Anyways, a bit more detail would probably help any potential 'answerer'.

And Yes, you could use command buttons on a worksheet to populate variables' values in a single userform. Personally, I would probably just pull up the userform and have a drop-down with all the stores' (names/store numbers/whatever) and let the user pick there.

Mark

 

[Pianoman23]

If you truly have only one worksheet with details for multiple stores, you might want to share how we are figuring out where an individual store's details reside in the sheet. Anyways, a bit more detail would probably help any potential 'answerer'.

And Yes, you could use command buttons on a worksheet to populate variables' values in a single userform. Personally, I would probably just pull up the userform and have a drop-down with all the stores' (names/store numbers/whatever) and let the user pick there.


Mark

Thanks for that Mark
The stores are referenced by column; in other words store 1 info is contained in columns D to I. Store 2 starts at column J to column O, etc.
Store Name for store 1 would be in D2 with the address from D3 to D7. Then follows the Tel No etc in subsequent rows with the detailed financial figures starting at D20 onwards.
Store 2 is organised the same way but with the name starting at J2.

 

[GTO]

Okay - while I am not sure about what's in all the columns, the store name and address seem to be in one column per store, so here's an example to get you going:

In a new standard workbook, where the first sheet has both the tab name and CodeName (defaults) of 'Sheet1', plunk this data in:

Excel Workbook
	A	B	C
1	Header	Header	Header
2	Ralph's Market	Mike's Market	Rose's Mart
3	123	456	789
4	West	West	North
5	Higland Way	Gibson Street	Center Street
6	Glendale	Boise	Somewhere
7	California	Idaho	Arkansas
Sheet1

Excel 2010

In a userform named UserForm1:

Add these controls:

Six (6) textbox controls.

Name these: lblStoreName, lblStreetAddress, lblDirection, lblStreetName, lblCityName and lblStateName.


Six (6) label controls.

Name these: txtStoreName, txtStreetAddress, txtDirection, txtStreetName, txtCityName and txtStateName.

One (1) commandbutton, named: cmdUnload

In the UserForm's Module:

Option Explicit
  
Private lShtCol As Long
  
Public Property Let SheetColumn(sc As Long)
  lShtCol = sc
End Property
  Public Property Get SheetColumn() As Long
    SheetColumn = lShtCol
  End Property
  
Private Sub cmdUnload_Click()
  Unload Me
End Sub
  
Private Sub UserForm_Activate()
Dim arrData
  
  With Sheet1
    arrData = .Range(.Cells(2, SheetColumn), .Cells(7, SheetColumn)).Value
  End With
  
  With Me
    .txtStoreName.Value = arrData(1, 1)
    .txtStreetAddress.Value = arrData(2, 1)
    .txtDirection.Value = arrData(3, 1)
    .txtStreetName.Value = arrData(4, 1)
    .txtCityName.Value = arrData(5, 1)
    .txtStateName.Value = arrData(6, 1)
  End With
  
End Sub
  
Private Sub UserForm_Initialize()
  
  With Me
    
    .Caption = vbNullString
    .Height = 198
    .Width = 299.25
    
    With .lblStoreName
      .Caption = "Store Name"
      .Height = 10
      .Left = 6
      .TextAlign = fmTextAlignRight
      .Top = 6
      .Width = 132
    End With
    With .lblStreetAddress
      .Caption = "Steet Address"
      .Height = 10
      .Left = 6
      .TextAlign = fmTextAlignRight
      .Top = 30
      .Width = 132
    End With
    With .lblDirection
      .Caption = "Direction (North, East, West, South)"
      .Height = 10
      .Left = 6
      .TextAlign = fmTextAlignRight
      .Top = 54
      .Width = 132
    End With
    With .lblStreetName
      .Caption = "Street Name"
      .Height = 10
      .Left = 6
      .TextAlign = fmTextAlignRight
      .Top = 78
      .Width = 132
    End With
    With .lblCityName
      .Caption = "City"
      .Height = 10
      .Left = 6
      .TextAlign = fmTextAlignRight
      .Top = 102
      .Width = 132
    End With
    With .lblStateName
      .Caption = "State"
      .Height = 10
      .Left = 6
      .TextAlign = fmTextAlignRight
      .Top = 126
      .Width = 132
    End With
    
    With .txtStoreName
      .Height = 18
      .Left = 138
      .Top = 2
      .Width = 150
    End With
    With .txtStreetAddress
      .Height = 18
      .Left = 138
      .Top = 26
      .Width = 150
    End With
    With .txtDirection
      .Height = 18
      .Left = 138
      .Top = 50
      .Width = 150
    End With
    With .txtStreetName
      .Height = 18
      .Left = 138
      .Top = 74
      .Width = 150
    End With
    With .txtCityName
      .Height = 18
      .Left = 138
      .Top = 98
      .Width = 150
    End With
    With .txtStateName
      .Height = 18
      .Left = 138
      .Top = 122
      .Width = 150
    End With
    
    With .cmdUnload
      .Accelerator = "u"
      .Caption = "Unload"
      .Height = 21.75
      .Left = 216
      .Top = 150
      .Width = 72
    End With
    
  End With 'Me
End Sub

In a Standard Module:

Option Explicit
  
Public Sub RunForm(SheetColumn As Long)
Dim frmStores As UserForm1
  
  Set frmStores = New UserForm1
  
  frmStores.SheetColumn = SheetColumn
  
  frmStores.Show
  
End Sub

On Sheet1, since our example has just three columns, three activex command buttons.

In Sheet1's Module:

Option Explicit
  
Private Sub CommandButton1_Click()
  RunForm 1
End Sub
  
Private Sub CommandButton2_Click()
  RunForm 2
End Sub
  
Private Sub CommandButton3_Click()
  RunForm 3
End Sub

I am sure there are other ways of doing this, but in short, we want set a reference to a new instance of the form (which will load the new instance) and then use a simple Property Let/Get to feed/retrieve a variable held in the form.

Hope that helps,

Mark

 

[Pianoman23]

Okay - while I am not sure about what's in all the columns, the store name and address seem to be in one column per store, so here's an example to get you going:

In a new standard workbook, where the first sheet has both the tab name and CodeName (defaults) of 'Sheet1', plunk this data in:

Sheet1[TABLE="class: html-maker-worksheet"]
<thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
</thead><tbody>[TR]
[TH]1[/TH]
[TD]Header[/TD]
[TD]Header[/TD]
[TD]Header[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]Ralph's Market[/TD]
[TD]Mike's Market[/TD]
[TD]Rose's Mart[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD="align: right"]123[/TD]
[TD="align: right"]456[/TD]
[TD="align: right"]789[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]West[/TD]
[TD]West[/TD]
[TD]North[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]Higland Way[/TD]
[TD]Gibson Street[/TD]
[TD]Center Street[/TD]
[/TR]
[TR]
[TH]6[/TH]
[TD]Glendale[/TD]
[TD]Boise[/TD]
[TD]Somewhere[/TD]
[/TR]
[TR]
[TH]7[/TH]
[TD]California[/TD]
[TD]Idaho[/TD]
[TD]Arkansas[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010



In a userform named UserForm1:

Add these controls:

Six (6) textbox controls.

Name these: lblStoreName, lblStreetAddress, lblDirection, lblStreetName, lblCityName and lblStateName.


Six (6) label controls.

Name these: txtStoreName, txtStreetAddress, txtDirection, txtStreetName, txtCityName and txtStateName.

One (1) commandbutton, named: cmdUnload

In the UserForm's Module:

Option Explicit
  
Private lShtCol As Long
  
Public Property Let SheetColumn(sc As Long)
  lShtCol = sc
End Property
  Public Property Get SheetColumn() As Long
    SheetColumn = lShtCol
  End Property
  
Private Sub cmdUnload_Click()
  Unload Me
End Sub
  
Private Sub UserForm_Activate()
Dim arrData
  
  With Sheet1
    arrData = .Range(.Cells(2, SheetColumn), .Cells(7, SheetColumn)).Value
  End With
  
  With Me
    .txtStoreName.Value = arrData(1, 1)
    .txtStreetAddress.Value = arrData(2, 1)
    .txtDirection.Value = arrData(3, 1)
    .txtStreetName.Value = arrData(4, 1)
    .txtCityName.Value = arrData(5, 1)
    .txtStateName.Value = arrData(6, 1)
  End With
  
End Sub
  
Private Sub UserForm_Initialize()
  
  With Me
    
    .Caption = vbNullString
    .Height = 198
    .Width = 299.25
    
    With .lblStoreName
      .Caption = "Store Name"
      .Height = 10
      .Left = 6
      .TextAlign = fmTextAlignRight
      .Top = 6
      .Width = 132
    End With
    With .lblStreetAddress
      .Caption = "Steet Address"
      .Height = 10
      .Left = 6
      .TextAlign = fmTextAlignRight
      .Top = 30
      .Width = 132
    End With
    With .lblDirection
      .Caption = "Direction (North, East, West, South)"
      .Height = 10
      .Left = 6
      .TextAlign = fmTextAlignRight
      .Top = 54
      .Width = 132
    End With
    With .lblStreetName
      .Caption = "Street Name"
      .Height = 10
      .Left = 6
      .TextAlign = fmTextAlignRight
      .Top = 78
      .Width = 132
    End With
    With .lblCityName
      .Caption = "City"
      .Height = 10
      .Left = 6
      .TextAlign = fmTextAlignRight
      .Top = 102
      .Width = 132
    End With
    With .lblStateName
      .Caption = "State"
      .Height = 10
      .Left = 6
      .TextAlign = fmTextAlignRight
      .Top = 126
      .Width = 132
    End With
    
    With .txtStoreName
      .Height = 18
      .Left = 138
      .Top = 2
      .Width = 150
    End With
    With .txtStreetAddress
      .Height = 18
      .Left = 138
      .Top = 26
      .Width = 150
    End With
    With .txtDirection
      .Height = 18
      .Left = 138
      .Top = 50
      .Width = 150
    End With
    With .txtStreetName
      .Height = 18
      .Left = 138
      .Top = 74
      .Width = 150
    End With
    With .txtCityName
      .Height = 18
      .Left = 138
      .Top = 98
      .Width = 150
    End With
    With .txtStateName
      .Height = 18
      .Left = 138
      .Top = 122
      .Width = 150
    End With
    
    With .cmdUnload
      .Accelerator = "u"
      .Caption = "Unload"
      .Height = 21.75
      .Left = 216
      .Top = 150
      .Width = 72
    End With
    
  End With 'Me
End Sub

In a Standard Module:

Option Explicit
  
Public Sub RunForm(SheetColumn As Long)
Dim frmStores As UserForm1
  
  Set frmStores = New UserForm1
  
  frmStores.SheetColumn = SheetColumn
  
  frmStores.Show
  
End Sub

On Sheet1, since our example has just three columns, three activex command buttons.

In Sheet1's Module:

Option Explicit
  
Private Sub CommandButton1_Click()
  RunForm 1
End Sub
  
Private Sub CommandButton2_Click()
  RunForm 2
End Sub
  
Private Sub CommandButton3_Click()
  RunForm 3
End Sub

I am sure there are other ways of doing this, but in short, we want set a reference to a new instance of the form (which will load the new instance) and then use a simple Property Let/Get to feed/retrieve a variable held in the form.

Hope that helps,

Mark

Wow!
Thanks for all your help Mark.
I did manage to solve the problem earlier today; but it was a bit cumbersome. It's getting late so I'll give your method a go in the morning.
Thanks again

 

[GTO]

You are most welcome