Textbox in Userform not recognised by Module

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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
 
Back
Top