Userform questions -- code included!

[Ray]

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

 

[Tom Ogilvy]

Userform_initialize should be in the userform module of your userform. It
is fired when the userform is loaded.

You should make your decision whether to show the userform before this code
is ever run

Put a command button from the Control Toolbox Toolbar on your sheet and
double click on it. It should take you to the click event. Add code like
the below to that event (in the sheet module for that worksheet):

Private Sub CommandButton1_Click()

Dim myR As Range
On Error Resume Next
Set myR = Worksheets("Admin").Range("G39:G68") _
.SpecialCells(xlCellTypeConstants)
On Error goto 0
if not myR is nothing then
Userform1.Show
Else
MsgBox "No stores were late!", vbExclamation
End if
End Sub

Now go back to the control toolbox toolbar and unclick the upper left icon
to take your sheet out of design mode (so the commandbutton will work).

Now in the useform Module, alter the Initialize event to something like this:

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

Set myR = Worksheets("Admin").Range("G39:G68") _
.SpecialCells(xlCellTypeConstants)
ReDim mySA(0 To myR.Cells.Count, 0 To 1)
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
End Sub

 

[Susan]

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

 

[Ray]

Thanks Tom .... your suggestions worked great! One follow-up question
(in the name of learning) -- my initial modification didn't take the
error-handling line out of the Userform code and when I tested the
code (without 'errors' in the data), the msgbox created by the error-
handling popped up anyway! Why would that have happened?

Susan -- your suggestions are very useful as well, especially from an
implementation standpoint! Your response is going into my 'code
archive' ...

Regards//Ray

 

[Tom Ogilvy]

the code is interpreted and executed sequentially.

If you put nothing in to prevent it, after running the regular code, it
would next enter and execute the error handling code (sounds like your
situation).

Most do this to avoid that

On error goto NoCells

' regular code that could raise an error

Exit sub
NoCells:
' error handling code

End Sub

So if there is not error, the NoCells section is never entered because of
the Exit sub.