Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform questions -- code included!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform questions -- code included!
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 -- Regards, Tom Ogilvy "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform questions -- code included!
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform questions -- code included!
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform questions -- code included!
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. -- Regards, Tom Ogilvy "Ray" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code modules included in sheet count? | Excel Programming | |||
how do i change or make this macro??? pictures and code included | Excel Programming | |||
how do i change or make this macro??? pictures and code included | Charts and Charting in Excel | |||
Run-time error '9' ---- Code to fix included. | Excel Programming | |||
Require extra funtionality - existing code included | Excel Programming |