Can anyone tell me why I get an error (Object required) on the line:

ActiveCell.Text = TextBox2.Text

The code is run from a UserForm and is intended to replace the contents of
the active cell with the text in TextBox2



Private Sub CommandButton7_Click()
Dim rng As Range

Dim aSht As Worksheet
Dim aCel As Range
Set aSht = ActiveSheet
Set aCel = ActiveCell
Application.ScreenUpdating = False

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

ActiveCell.ClearContents
ActiveCell.Text = TextBox2.Text


aSht.Activate
aCel.Select

Application.ScreenUpdating = True
End Sub

Use ActiveCell.Value instead of ActiveCell.Text... the Text property for a
Range object is Read Only.

Rick


"Patrick C. Simonds" wrote in message
...
Can anyone tell me why I get an error (Object required) on the line:

ActiveCell.Text = TextBox2.Text

The code is run from a UserForm and is intended to replace the contents of
the active cell with the text in TextBox2



Private Sub CommandButton7_Click()
Dim rng As Range

Dim aSht As Worksheet
Dim aCel As Range
Set aSht = ActiveSheet
Set aCel = ActiveCell
Application.ScreenUpdating = False

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

ActiveCell.ClearContents
ActiveCell.Text = TextBox2.Text


aSht.Activate
aCel.Select

Application.ScreenUpdating = True
End Sub

Hi Patrick,

My first guess would be that you need to set the TakeFocusOnClick
property of the CommandButton to False.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

"Patrick C. Simonds" wrote in message
...
Can anyone tell me why I get an error (Object required) on the line:

ActiveCell.Text = TextBox2.Text

The code is run from a UserForm and is intended to replace the contents of
the active cell with the text in TextBox2



Private Sub CommandButton7_Click()
Dim rng As Range

Dim aSht As Worksheet
Dim aCel As Range
Set aSht = ActiveSheet
Set aCel = ActiveCell
Application.ScreenUpdating = False

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

ActiveCell.ClearContents
ActiveCell.Text = TextBox2.Text


aSht.Activate
aCel.Select

Application.ScreenUpdating = True
End Sub

Anything like:

Sub fhakf()
ActiveCell.Text = "xxx"
End Sub

will fail since Text is read-only for Ranges.


Use ActiveCell.Value
--
Gary''s Student - gsnu200797


"Patrick C. Simonds" wrote:

Can anyone tell me why I get an error (Object required) on the line:

ActiveCell.Text = TextBox2.Text

The code is run from a UserForm and is intended to replace the contents of
the active cell with the text in TextBox2



Private Sub CommandButton7_Click()
Dim rng As Range

Dim aSht As Worksheet
Dim aCel As Range
Set aSht = ActiveSheet
Set aCel = ActiveCell
Application.ScreenUpdating = False

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

ActiveCell.ClearContents
ActiveCell.Text = TextBox2.Text


aSht.Activate
aCel.Select

Application.ScreenUpdating = True
End Sub