only a userform can call code from behind the userform.
if you want to make it "callable" you have to make a few changes.


Private Sub Userform_Initialize()

call Stores_R_Late

end sub


public sub Stores_R_Late()

Dim myR As Range
Dim myCell As Range
Dim mySA() As String
Dim i As Integer

On Error GoTo NoCells

Set myR = Worksheets("Admin").Range("G39:G68") _
.SpecialCells(xlCellTypeConstants)
' Load UserForm1
ReDim mySA(0 To myR.Cells.Count, 0 To 1)
On Error GoTo 0
i = 0
For Each myCell In myR
mySA(i, 0) = myCell.Value
mySA(i, 1) = myCell(1, 2).Value
i = i + 1
Next myCell
UserForm1.ListBox1.List = mySA
' UserForm1.Show

NoCells:
MsgBox "No stores were late!", vbExclamation
Exit Sub
End Sub


in the VB editor, choose Insert Module from the toolbar. put the sub
Stores_R_Late in the module. now not only can you access it from the
userform, but it is available to any other process in that workbook.

to assign a command button, from the control toolbox toolbar, add a
command button. Right click & choose properties to change the caption
to whatever you want (& size & other properties). now double click
it. the VB editor will open up & tell you:

Private Sub CommandButton1_click()

End sub

in between there you would put

Private Sub CommandButton1_click()
call Stores_R_Late
End sub

IMPORTANT! when done editing the command button, you must exit
"design mode." the design mode button on the toolbox toolbar has a
triangle, a ruler & a pencil. click that.

now when you click on your button, it will run the code.
hope this helps!
susan


On Aug 6, 8:14 am, Ray wrote:
Hello -

I recently got some help in building the code below (thanks Bernie!),
but have a couple of questions about it ...

1) how do I call it from a Button? I tried changing the name of the
button to match the Sub name and also tried 'Calling' the Sub from the
button, but neither way worked for me ...

2) On Error, the code below should display a short msgbox and then
then Exit when the user clicks "OK" -- instead, the code continues
(and shows an empty userform) upon hitting OK -- why and how do I fix
it?

3) As part of my testing, I changed the Sub name to
'test_initialize()' -- the blank userform popped rigtht up, but the
msgbox was totally skipped (even though no data was present).
Changing back to 'Userform_Initialize' fixed the problem ... is this
just a built-in property of userforms?

Please forgive the 'basic' questions ... I'm just starting to use
userforms!

TIA,
Ray

Private Sub Userform_Initialize()
Dim myR As Range
Dim myCell As Range
Dim mySA() As String
Dim i As Integer

On Error GoTo NoCells

Set myR = Worksheets("Admin").Range("G39:G68") _
.SpecialCells(xlCellTypeConstants)
' Load UserForm1
ReDim mySA(0 To myR.Cells.Count, 0 To 1)
On Error GoTo 0
i = 0
For Each myCell In myR
mySA(i, 0) = myCell.Value
mySA(i, 1) = myCell(1, 2).Value
i = i + 1
Next myCell
UserForm1.ListBox1.List = mySA
' UserForm1.Show

NoCells:
MsgBox "No stores were late!", vbExclamation
Exit Sub
End Sub