I have a listbox that will show the range containing the choices, but only if the sheet with the range of choices is the active sheet. How to have the list box show the choices no matter what sheet is active. Below is my code for the userform. Form name is "samform". Listbox name is "sambox" The range containing the choices is "samlist"
Thanks for any help
VBA Code:
Sub show_samform()
samform.Show modal
With samform
.Height = 300
.Width = 280
.Caption = "show_samform"
With samform.Label1
.Caption = "Enter"
.Top = 2
.Width = 200
.Height = 30
.Font.Size = 20
.Font.Bold = True
.ForeColor = vbBlue
.Left = (samform.InsideWidth - .Width) / 2
.TextAlign = fmTextAlignCenter
End With
With samform.Label2
.Caption = Range("ReportName").Value
.Top = 32
.Width = 280
.Height = 30
.Font.Size = 20
.Font.Bold = True
.ForeColor = vbRed
.Left = (samform.InsideWidth - .Width) / 2
.TextAlign = fmTextAlignCenter
End With
With samform.Label3
.Caption = "Samples"
.Top = 62
.Width = 200
.Height = 30
.Font.Size = 20
.Font.Bold = True
.ForeColor = vbBlue
.Left = (samform.InsideWidth - .Width) / 2
.TextAlign = fmTextAlignCenter
End With
With samform.sambox
.Top = 102
.Width = 100
.Height = 120
.Left = (samform.InsideWidth - .Width) / 2
.RowSource = Range("samlist").Address
.Font.Size = 10
.Font.Bold = True
.TextAlign = fmTextAlignCenter
.MultiSelect = fmMultiSelectMulti
.SetFocus
End With
With samform.samform_but
.Top = 224
.AutoSize = True
.Caption = "OK"
.Font.Size = 14
.Font.Bold = True
.ForeColor = vbBlack
.Left = (samform.InsideWidth - .Width) / 2
End With
End With
Do While samform.Visible
DoEvents
Loop
End Sub
Thanks for any help