VBA code causes Find and Replace dialog box to lose focus
 

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

VBA code causes Find and Replace dialog box to lose focus
 

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" wrote:

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

VBA code causes Find and Replace dialog box to lose focus
 

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


"Barb Reinhardt" wrote:

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" wrote:

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