running program from userform

[davegb]

Some time ago, in a series of threads, I learned that if a program is
always run in conjunction with a userform, the code should all be in
the userform, not in a module. I was too far along with the previous
program to re-write it all to be in the userform.

Now I'm writing another program that will only be run with the userform
(uf1021Mid), so I'm trying to write the code entirely in the userform.
So far, I have 2 problems. One is, how do I initiate a program in the
userform? Normally, when I initiate a program, I call it's name from
the macro menu or from a tool to which it's been assigned. Since the
code in the userform already has names like "Private Sub
btnCancel_Click()", I don't think I can call the program from those
names. So how do I access the macro? Maybe I misunderstood the previous
posts and there always has to be at least the Sub name and a userform
call in a module?

Second problem in testing to see if the refedit in the userform is
returning a range. I'm using the following code, the test code being
from Walkenbach's book:

Private Sub OKButton_Click()


Dim wbExtr As Workbook

Dim wsRef As Worksheet 'wks where Top 10 list is stored
Dim wsExtFrom As Worksheet 'Wks where data is extracted from
Dim wsTop As Worksheet 'wks where new table goes

Dim oWS As Object

Dim rCopy As Range
Dim rCell As Range 'each cell in rRef
Dim rRef As Range 'Range on wsRef where current CtyLst is
Dim rExtrFromStrt As Range
Dim rExtrFrom As Range 'range in Src sheet Where cty names are
Dim rRefRow As Range
Dim rExtrFromEnd As Range
'Dim rColHdr As Range
Dim rHdrStrt As Range
Dim rHdrEnd As Range

Dim s1stCtyName As String
Dim sUCrCell As String
Dim sCtyName As String
Dim sHeader As String

Dim lExtrFromCol As Long 'CtyCol in Src sht
Dim lExtrToCol As Long
Dim lTopRow As Long
Dim lCopyRow As Long
Dim lBOS10Row As Long
Dim lBOS21Row As Long
Dim lStrDif As Long
Dim lMid3Row As Long
Dim lBOS3Row As Long
Dim lRefRow As Long
Dim lExtrFromStrt As Long
Dim lColHdrCount As Long
Dim lLastCol As Long
Dim lLastRow As Long


Dim bHdr As Boolean

Set wsRef = Workbooks("Mark Top 10.xls").Worksheets("CtyLst")
Set wsExtFrom = ActiveSheet
Set wbExtr = ActiveWorkbook
lTopRow = 2
lBOS10Row = 14
lBOS21Row = 25
lMid3Row = 14
lBOS3Row = 27

'Test is Mark Top 10 workbook is active
If ThisWorkbook.Name = wbExtr.Name Then
MsgBox "You have selected the workbook that contains the macro." &
_
Chr(13) & "Please click Ok and select the correct workbook and " &
_
Chr(13) & "worksheet and restart the macro.", vbOKOnly
Exit Sub

End If



'TEST FOR SHEET NAMED "Top"
For Each oWS In wbExtr.Sheets
If oWS.Name = "Top" Then
If MsgBox("A worksheet named Top already exists in this
workbook." _
& Chr(13) & "Please remove or rename it and run the macro
again.", _
vbOKOnly) = vbOK Then Exit Sub
End If
Next
On Error Resume Next
'Set rColHdr = ActiveSheet.Range(reDataStrt.Text)
Set uf1021Mid.rColHdr = Range(reDataStrt.Text)

If Error <> 0 Then
MsgBox "Invalid range selection, please select the starting range
again."
On Error GoTo 0
Exit Sub
End If
uf1021Mid.Hide
End Sub

No matter what I enter in the refedit control, I get the error message.
Any ideas?

Thanks as always.

 

[Nigel]

Your event code must be on the userform, so when a control is clicked or
changed etc. the private sub will act on these events
from these you can call functions or other subs in external modules that
carry out the steps required.

Private Sub btnCancel_Click
Call mySub ' in another module
myValue = myFunction ' in another module
End Sub

If you wish to act on the control on the userform, then that code could be
placed in the userform or the name of the userform passed to the sub routine
as an object parameter, this method allows code to be reusable for different
forms, something like....

Dim myForm as Object
Private Sub btnCancel_Click
Set myForm = Me.Name
Call mySub (myForm)
End Sub

 

[davegb]

Your event code must be on the userform, so when a control is clicked or
changed etc. the private sub will act on these events
from these you can call functions or other subs in external modules that
carry out the steps required.

Private Sub btnCancel_Click
Call mySub ' in another module
myValue = myFunction ' in another module
End Sub

If you wish to act on the control on the userform, then that code could be
placed in the userform or the name of the userform passed to the sub routine
as an object parameter, this method allows code to be reusable for different
forms, something like....

Dim myForm as Object
Private Sub btnCancel_Click
Set myForm = Me.Name
Call mySub (myForm)
End Sub

Thanks for your reply, Nigel. Unfortunately, you didn't answer either
of my questions. Can anyone answer my questions?

 

[Susan]

i can help you with the 1st question, if i understand what you want.
i'm still a learner myself, so bear with me.
i do (what i think you want) all the time..........

in a separate module, called usually GlobalMods, i
declare all my variables & stuff as PUBLIC. then i
write any mini-routines in there, too.
then, in the userform module, i write my main macro
connected with the click() event button on the macro.
when i need to call the mini-routine in the middle of
the main macro, i just call it & it switches over there
& runs.
example:
xxxxxxxxxxxxxxxxxxx
Global Mods:
Public r as Range
Public txtFee as Control


public sub print()
application.displayalerts=false
activeworksheet.printout
activewindow.close
application.displayalerts=true
end sub

--------------------------
userform module:

Sub OK_Click()
blah blah blah
whatever you want to do

Call Print

end sub
xxxxxxxxxxxxxxxxxxxxxxx
you can "call" as many mini-subs as you want
from the main macro.
i hope this is what you were talking about.
susan

 

[davegb]

i can help you with the 1st question, if i understand what you want.
i'm still a learner myself, so bear with me.
i do (what i think you want) all the time..........

in a separate module, called usually GlobalMods, i
declare all my variables & stuff as PUBLIC. then i
write any mini-routines in there, too.
then, in the userform module, i write my main macro
connected with the click() event button on the macro.
when i need to call the mini-routine in the middle of
the main macro, i just call it & it switches over there
& runs.
example:
xxxxxxxxxxxxxxxxxxx
Global Mods:
Public r as Range
Public txtFee as Control


public sub print()
application.displayalerts=false
activeworksheet.printout
activewindow.close
application.displayalerts=true
end sub

--------------------------
userform module:

Sub OK_Click()
blah blah blah
whatever you want to do

Call Print

end sub
xxxxxxxxxxxxxxxxxxxxxxx
you can "call" as many mini-subs as you want
from the main macro.
i hope this is what you were talking about.
susan

Thanks for your reply. I guess I'm not making myself clear. I know I
can call a macro in a module from a userform. I know I can show a
userform from a macro in a module. What I'm asking is, do I have to
have at least some code in a module to access the userform? Is there a
way to directly go to a userform without going through some code in a
module?

I hope that's more clear on question 1. I'll repost on question 2.

 

[Susan]

ok. what do you mean by "access"?
being able to type in the form?
or being able to retrieve information from the form?
susan

 

[davegb]

ok. what do you mean by "access"?
being able to type in the form?
or being able to retrieve information from the form?
susan

I mean showing the form for the user to select options, type in text,
select a cell or range, make a selection from a combo box, etc. Any of
the things that an end user would do in a userform. Does the userform
have to be called from code in a module, or is there any other way to
show a userform?

 

[Susan]

ahhhh..... light dawns on marble-head.
yes, a userform has to be called from code
within a module.

you can either use an auto_open sub in a
module, in which case the userform will
automatically pop up when the file opens
up, or you can put a different sub in a
module.
xxxxxxxxx
Public Sub auto_open()

Load Userform1
Userform1.Show

End Sub
xxxxxxxxxxxxxxx
if you want the user to choose when it loads,
you can put an autoshape in the spreadsheet,
with text on it like "Click this to add a person."...
then right-click the autoshape & choose "Assign
Macro." Then when they click the autoshape the
userform will pop up.

you will also need a "Userform1_Initialize()"
sub in your userform module, like this:
xxxxxxxxxxxxxx
Sub frmSched_initialize()

Dim oControl As Control

For Each oControl In Me.Controls
If TypeOf oControl Is msforms.TextBox Then
oControl.Value = ""
ElseIf TypeOf oControl Is msforms.OptionButton Then
oControl.Value = False
End If
Next oControl

MultiPage1("pgSchedule").txtClient.SetFocus


End Sub
xxxxxxxxxxxxxxxxxxxx
this initialization sub sets all my textboxes
blank & option buttons as false, then
sets the cursor in the txtClient textbox.

glad i could finally answer (at least one) of
your questions.
susan

 

[davegb]

ahhhh..... light dawns on marble-head.

LOL! It's more about my phrasing of the question than your marbles, or
lack thereof.

yes, a userform has to be called from code
within a module.

Thank-you, thank-you, thank-you!

 

[Susan]

i'm glad it's starting to work!
can you post your final code (when you're done) so in the future when
somebody's looking for the answer to the same question, they can see
what/how you did it?
thanks
susan

 

[davegb]

i'm glad it's starting to work!
can you post your final code (when you're done) so in the future when
somebody's looking for the answer to the same question, they can see
what/how you did it?
thanks
susan

Susan,
Normally I would be happy to, but in this case, there is no final code
to post, it's where the code is that's relevant, not the code itself.
Thanks for your help.
Dave