Excel VBA

[Guest]

Hi I am new to VBA in ecel and have a question. I have made a userform to
collect information from the user and with that information I am using the
following code:

Set foundCell = Range("a4:a50").Find(Prob, , xlValues, xlWhole)
If foundCell Is Nothing Then
Check = MsgBox("Not Found. Would you like to add it to the database?",
36, "ERROR")
If Check = vbYes Then
thing.Hide
info.Show
Else
Exit Sub
End If
Else .....

I want to use data in the row of my 'foundcell'. My question is:
HOW DO I PASS THE INFORMATION ABOUT THE FOUNDCELL AND ALL THE OTHER CELLS IN
THE ROW, TO OTHER SUB PROCEEDURES AND FUNCTION PROCEEDURES THAT I HAVE
WRITTEN IN THE MODULE?

Thank you

 

[Guest]

Hi

I don't know but can't you not use
Public foundCell as string
Alvin


"trini" skrev:

 

[Guest]

Sorry but I am really new. Huh? About foundcell being a string. The code I
have written works. Can you please explain.
Thanks
Amanda

 

[Harald Staff]

Hi

Pass variables like this:

Sub test()
Dim foundCell As Range
Set foundCell = ActiveCell
Call Test1(foundCell)
Call Test2(foundCell)
End Sub

Sub Test1(R As Range)
MsgBox "I received the range " & R.Address, , "Test 1 says:"
End Sub

Sub Test2(R As Range)
MsgBox "I received a cell in row " & R.Row, , "Test 2 says:"
End Sub

HTH. Best wishes Harald

 

[Guest]

Thank you. I will try it, but will this only work if all of the code you
supplied was in the module, or can I put some of the code in the code for the
userform (in other words can I put some of it for the macro when you hit the
command button-search?)

 

[Bob Phillips]

Just use Public variables, like Alvin suggested, as these can be accessed
anywhere. Just ensure that you declare them in the code module, not the
Userform class module.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jamie Collins]

Just use Public variables

And for those of us who shun Public variables <g>, create a Public
Property for the userform, prevent the userform from unloading itself
(hide it instead) and test the value of the Property e.g.

' ---<Userform1 code module>---
Option Explicit
Private m_foundCell As Range

Private Function FindCell()
' Code to find cell goes here e.g.
Set m_foundCell = Range("A1")
End Function

Public Property Get foundCell() As Range
Set foundCell = m_foundCell
End Property

Private Sub UserForm_QueryClose( _
Cancel As Integer, _
CloseMode As Integer)
Me.Hide
Cancel = True
End Sub
' ---</Userform1 code module>---

' ---<Calling code >---
Sub test()
Dim rng As Range
Dim thing As UserForm1
Set thing = New UserForm1
With thing
.Show vbModal
Set rng = .foundCell
End With
Set thing = Nothing

' code continues ...
If rng Is Nothing Then
End If

End Sub

Jamie.

--