Hi

I'm having trouble getting my Module procedures recognising the values from a Userform

A simple example is where I have a Userform with a textbox ("txtTest") and a command button ("cmdRun"). The code for cmdRun is as follows

----------------------------------
Private Sub cmdRun_Click(

Call MyMacr

End Su
----------------------------------

The MyMacro procedure resides in a standard code module, as follows

----------------------------------
Sub MyMacro(

Dim strTest as Strin

strTest=txtTest.Valu

End Su
----------------------------------

However, when I run this, I get an error "Object Required", i.e. the MyMacro is not recognising the textbox on the userform

Could someone please help

Thanks heaps

SuperJas.

The Modules don't recognize the controls on a Userform. There are several things you can do.

1) Put the MyMacro code in the Userform code
o
2) Pick a cell on the worksheet to pass the value to. Then pick it up from sheet in MyMacr

Private Sub txtTest_Change() Instead of this sub you could also set the ControlSource fo
range("A1") = txtTest.value the textbox in the userform to a particular cell in the worksheet
End Sub Just put A1 in the controlsource line in the properties window

Sub MyMacro(
Dim strTest as Strin
strTest = range("A1"



3) Probably the best way is to add the variable to your macro nam
This assumes MyMacro is on Module

Sub MyMacro (str
strTest = st
end su

' The code on your userform could look like thi
Private Sub cmdRun_Click(
Dim Temp as strin
Temp = txtTest.valu
If Temp < vbNullString then ' This would not let the macro to be called if the textbox was empt
Module1.MyMacro Temp ' If you add the variable to the macro name on Module1 then as soon
end if ' as you type ' Module1. ' on the userform code, you should see a window open
End Sub ' with MyMacro as a choice. Then when you add that and have Module1.MyMacr
' you should see str meaning that a value needs to be in the line. Temp wil
' pass the value to st

I hope I'm being clear for you. I'm not a programmer but I've been doing a lot with Userforms

Hi Rich

Fantastic! Option 3 worked like a charm

Thanks

SuperJas.

Sub MyMacro()

Dim strTest as String

strTest=Userform1.txtTest.Value

End Sub

Where Userform1 is the name of your Userform.
Userforms are in effect Private Modules so you need to Explicitly
reference the Userform ... hence your Error.


"SuperJas" wrote in message ...
Hi,

I'm having trouble getting my Module procedures recognising the values from a Userform.

A simple example is where I have a Userform with a textbox ("txtTest") and a command button ("cmdRun"). The code for cmdRun is as follows:

-----------------------------------
Private Sub cmdRun_Click()

Call MyMacro

End Sub
-----------------------------------

The MyMacro procedure resides in a standard code module, as follows:

-----------------------------------
Sub MyMacro()

Dim strTest as String

strTest=txtTest.Value

End Sub
-----------------------------------

However, when I run this, I get an error "Object Required", i.e. the MyMacro is not recognising the textbox on the userform.


Could someone please help?

Thanks heaps,

SuperJas.

From a programmer's perspective, if your sub procedure requires a
value or an object then pass it in as a parameter. Give you're sub
procedure an argument. If your sub procedure just needs the text, pass
in the text e.g.

Public Sub MyMacro(ByVal Text As String)
' <Do something with Text here
End Sub

Private Sub cmdRun_Click()
MyMacro txtTest.Text
End Sub

However, if your sub procedure needs to operates on the textbox object
itself, then use the textbox as the argument (i.e. pass a pointer to
the textbox) e.g.

Public Sub MyMacro(ByVal oTextBox As MSForms.TextBox)
' <Do something with oTextBox here
End Sub

Private Sub cmdRun_Click()
MyMacro txtTest
End Sub

--

Rich J wrote in message ...
The Modules don't recognize the controls on a Userform. There are several things you can do.

1) Put the MyMacro code in the Userform code.
or
2) Pick a cell on the worksheet to pass the value to. Then pick it up from sheet in MyMacro

Private Sub txtTest_Change() Instead of this sub you could also set the ControlSource for
range("A1") = txtTest.value the textbox in the userform to a particular cell in the worksheet.
End Sub Just put A1 in the controlsource line in the properties window.

Sub MyMacro()
Dim strTest as String
strTest = range("A1")
.
.

3) Probably the best way is to add the variable to your macro name
This assumes MyMacro is on Module1

Sub MyMacro (str)
strTest = str
end sub

' The code on your userform could look like this
Private Sub cmdRun_Click()
Dim Temp as string
Temp = txtTest.value
If Temp < vbNullString then ' This would not let the macro to be called if the textbox was empty
Module1.MyMacro Temp ' If you add the variable to the macro name on Module1 then as soon
end if ' as you type ' Module1. ' on the userform code, you should see a window open
End Sub ' with MyMacro as a choice. Then when you add that and have Module1.MyMacro
' you should see str meaning that a value needs to be in the line. Temp will
' pass the value to str

I hope I'm being clear for you. I'm not a programmer but I've been doing a lot with Userforms

Even better better way to do it. I forgot to try referencing the Userform first. And I've called macros in the Userform in the same way too. ie. Userform1.Macro1 Good call Ivan