Object Variable error

[Patrick C. Simonds]

I use the following code below to populate the current cell on the January
worksheet (there is a worksheet for each month, and each month has to have
all the names that appear on the ParaTransit Names worksheet) with the name
that is entered in the referenced cell on the ParaTransit Names worksheet:

=IF('ParaTransit Names'!B15>"",'ParaTransit Names'!B15,"")


I then use the following code to place the name contained in the active cell
on the January worksheet in the TextBox of the UserForm:


Private Sub UserForm_Initialize()

Dim rng
Set rng = Cells(ActiveCell.Row, 1)

'Places date in box at top of userform

TextBox1.Text = rng(1, 2).Text

End Sub


What I was hoping for, is that when I ran the code below it would search out
and select the cell matching the TextBox value on the ParaTransit Names
worksheet. What I get is an Error Msg "object variable or With block
variable not set". Any guidance here?




Private Sub CommandButton1_Click()

With ActiveWorkbook.Worksheets("ParaTransit Names")

Cells.Find(What:=Me.TextBox1.Text, After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate

End With

End Sub

 

[Joel]

A cell doesn't have TEXT it should be VALUE.

TextBox1.Text = rng(1, 2).Value

 

[Patrick C. Simonds]

Thanks, I changed that, but it still does not solve the problem.

 

[ward376]

Private Sub CommandButton1_Click()
ThisWorkbook.Sheets("paratransit names").Activate
Cells.Find(What:=TextBox1.Text, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
End Sub

Private Sub UserForm_Initialize()
Dim rng
Set rng = Cells(ActiveCell.Row, 1)

TextBox1.Text = rng.Value

End Sub

Cliff Edwards

 

[Bob Phillips]

Try this

Private Sub cmdOK_Click()
Dim rng As Range

With ActiveSheet 'ActiveWorkbook.Worksheets("ParaTransit Names")

Set rng = Cells.Find(What:=CDate(Me.TextBox1.Text), _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True, _
SearchFormat:=False)
If Not rng Is Nothing Then rng.Activate
End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Of course it does, it has Value, Value2 and Text. Check you help.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Patrick C. Simonds]

I get the same error msg.

I case it was not clear as to what I am trying to do, I want this code to
run without actually making the worksheet "paratransit names" active. The
code below does what I need to the extent that it finds the text, but at the
expense of making the "paratransit names" worksheet active.

Maybe it is not possible to perform this function without making the
worksheet active. If that is the case, I need something to bring me back to
the cell on the worksheet which was active when the code was initiated.




Private Sub CommandButton1_Click()

ThisWorkbook.Sheets("paratransit names").Activate

Cells.Find(What:=TextBox1.Text, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

Unload UserForm1

End Sub

 

[ward376]

I couldn't find a way to use find without activating the sheet, just
store your active sheet/cell in variables before the find and return
to them after.

Private Sub CommandButton1_Click()
Dim aSht As Worksheet
Dim aCel As Range
Set aSht = ActiveSheet
Set aCel = ActiveCell

ThisWorkbook.Sheets("paratransit names").Activate
Cells.Find(What:=TextBox1.Text, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate

aSht.Activate
aCel.Select
Unload Me
End Sub


Private Sub UserForm_Initialize()
Dim rng
Set rng = Cells(ActiveCell.Row, 1)

TextBox1.Text = rng.Value

End Sub


Cliff Edwards

 

[Dave Peterson]

Your commandbutton1 is a commandbutton from the Control toolbox toolbar placed
on a worksheet, right?

If that's true, then your unqualified ranges (like cells in cells.find) will
refer to the sheet that owns the code--not necessarily the activesheet.

Option Explicit
Private Sub CommandButton1_Click()
dim FoundCell as range

with ThisWorkbook.Sheets("paratransit names")
set foundcell = .Cells.Find(What:=me.TextBox1.Text, _
After:=.cells(.cells.count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
end with
if foundcell is nothing then
'what happens
else
application.goto foundcell
end if

End Sub

 

[Patrick C. Simonds]

No, sorry it is on a UserForm

Your commandbutton1 is a commandbutton from the Control toolbox toolbar
placed
on a worksheet, right?

If that's true, then your unqualified ranges (like cells in cells.find)
will
refer to the sheet that owns the code--not necessarily the activesheet.

Option Explicit
Private Sub CommandButton1_Click()
dim FoundCell as range

with ThisWorkbook.Sheets("paratransit names")
set foundcell = .Cells.Find(What:=me.TextBox1.Text, _
After:=.cells(.cells.count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
end with
if foundcell is nothing then
'what happens
else
application.goto foundcell
end if

End Sub

 

[Dave Peterson]

I would still use the same code (with the "unload me" line added).

You can't go wrong by qualifying your ranges.