Excel VBA
 

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

Excel VBA
 

Hi

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


"trini" skrev:

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

Excel VBA
 

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

"Alvin Hansen" wrote:

Hi

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


"trini" skrev:

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

Excel VBA
 

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

"trini" skrev i melding
...
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

Excel VBA
 

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?)

"Harald Staff" wrote:

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

"trini" skrev i melding
...
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

Excel VBA
 

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)


"trini" wrote in message
...
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?)

"Harald Staff" wrote:

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

"trini" skrev i melding
...
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

Excel VBA
 

Bob Phillips wrote:
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.

--