VBA code causes Find and Replace dialog box to lose focus

[shabutt]

Dear friends,

I have below event code in my worksheet which works perfectly but causes 2
known problems, i.e.,

1- "Find and Replace" dialog box loses focus once I do find search in the
worksheet.
2- When I delete a row in the worksheet, the code takes me to the end/debug
dialog box and on clicking end on this dialog box, selects the textbox I have
placed in my worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If Not rngLast Is Nothing Then
ActiveSheet.Shapes("TextBox").Select
Selection.Characters.Text = rngLast(1, 6).Value
End If
Set rngLast = Target
rngLast.Select
Application.ScreenUpdating = True
Shapes("TextBox").SetShapesDefaultProperties
End Sub

I am using win xp sp3 and office 2007. I have limited knowledge about vba
and need your kind help.

TIA
Shahbaz

 

[Barb Reinhardt]

It losing focus because you are telling it to do so. Also, include Option
Explicit before all of your code. It forces you to declare all variables,
and while it's a PITA initially, it saves a lot of headaches. Try this.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False

'rngLast is not defined, or is it a public variable
Dim rngLast As Range

If Not rngLast Is Nothing Then

'Change
'ActiveSheet.Shapes("TextBox").Select
'Selection.Characters.Text = rngLast(1, 6).Value
'to
Me.Shapes("TextBox").TextFrame.Characters.Text = rngLast(1, 6).Text


End If
Set rngLast = Target

'You don't need to select it because nothing else has been selected
'rngLast.Select
Application.ScreenUpdating = True
Shapes("TextBox").SetShapesDefaultProperties
End Sub

 

[shabutt]

Thank you Barb Reinhardt for your help & guidance. Here is the modified code
which causes no problems but I don't know if it's really neat & clean and
whether it's fast.

Option Explicit
Dim rngLast As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
On Error Resume Next
If Not rngLast Is Nothing Then
Me.Shapes("TextBox").TextFrame.Characters.Text = rngLast(1, 6).Value

End If
Set rngLast = Target
Application.ScreenUpdating = True

Shapes("TextBox").SetShapesDefaultProperties
On Error GoTo 0
End Sub

TIA
Shahbaz