I have 5 textboxes on a sheet and a number of Checkboxes the names of
which are the opening statements that I want pasted in "the next
available" textbox.... ie. Textbox1 is named.....
"Mechanisation problem " Using Application.caller and the macro below
I can paste this into a free textbox to start the user off on a
pre-ordained fault script and by selecting the same Checkbox again
clear the box.... so far so good all works well until... ( and it would
appear this is, as we say, a "Hot Potatoe") I can't get focus STABLE
on the textbox? The strange thing is if I repeat the sequence (select
to remove then immediately select again focus is achieved) I have tried
dipping-out to a cell prior to running the main macro to establish a
common "launch-point" but this is ignored..... gone so far and stumped
at the finishing post .... Any help appreciated.


Sub setup3()
Application.ScreenUpdating = False
Dim cbo As MSForms.TextBox
Dim obj As OLEObject
Dim Message As Variant

Message = Application.Caller
ChckLength = Len(Application.Caller)

'ActiveSheet.Range("K8").Select this does nothing !!!!

With ActiveSheet
For Each obj In .OLEObjects
If TypeName(obj.Object) = "TextBox" Then
If Left(obj.Object.Text, ChckLength) = Message Then
obj.Object.Text = ""
Exit Sub
End If
End If
Next
End With
ActiveSheet.Range("K8").Select ' same here !!!!!
With ActiveSheet
For Each obj In .OLEObjects
If TypeName(obj.Object) = "TextBox" Then
Set cbo = obj.Object
Set TB = ActiveSheet.OLEObjects(cbo.Name)
If Trim(obj.Object.Text) = "" Then
obj.Object.Text = Message & " "
GoTo ending
End If
End If
Next
End With
ending:
cbo.Select
Selection.Activate

End Sub

I am not clear on what your objective is here, and what is or is not
happening.

When I select a checkbox, the caption is loaded into a textbox, AND the
focus is in that textbox. If I uncheck it, the textbox is cleared and there
is nothing to focus on. Similalrly, If I select a cell, the textbox loses
the focus.

Bob

wrote in message
oups.com...
I have 5 textboxes on a sheet and a number of Checkboxes the names of
which are the opening statements that I want pasted in "the next
available" textbox.... ie. Textbox1 is named.....
"Mechanisation problem " Using Application.caller and the macro below
I can paste this into a free textbox to start the user off on a
pre-ordained fault script and by selecting the same Checkbox again
clear the box.... so far so good all works well until... ( and it would
appear this is, as we say, a "Hot Potatoe") I can't get focus STABLE
on the textbox? The strange thing is if I repeat the sequence (select
to remove then immediately select again focus is achieved) I have tried
dipping-out to a cell prior to running the main macro to establish a
common "launch-point" but this is ignored..... gone so far and stumped
at the finishing post .... Any help appreciated.


Sub setup3()
Application.ScreenUpdating = False
Dim cbo As MSForms.TextBox
Dim obj As OLEObject
Dim Message As Variant

Message = Application.Caller
ChckLength = Len(Application.Caller)

'ActiveSheet.Range("K8").Select this does nothing !!!!

With ActiveSheet
For Each obj In .OLEObjects
If TypeName(obj.Object) = "TextBox" Then
If Left(obj.Object.Text, ChckLength) = Message Then
obj.Object.Text = ""
Exit Sub
End If
End If
Next
End With
ActiveSheet.Range("K8").Select ' same here !!!!!
With ActiveSheet
For Each obj In .OLEObjects
If TypeName(obj.Object) = "TextBox" Then
Set cbo = obj.Object
Set TB = ActiveSheet.OLEObjects(cbo.Name)
If Trim(obj.Object.Text) = "" Then
obj.Object.Text = Message & " "
GoTo ending
End If
End If
Next
End With
ending:
cbo.Select
Selection.Activate

End Sub

Bob Phillips wrote:
I am not clear on what your objective is here, and what is or is not
happening.

When I select a checkbox, the caption is loaded into a textbox, AND the
focus is in that textbox. If I uncheck it, the textbox is cleared and there
is nothing to focus on. Similalrly, If I select a cell, the textbox loses
the focus.

Bob thanks for the reply...........


Bob the intention is to have the user select one or more CheckBox
option/s The caption is the leader text for the rest of the user input
into that box. There are 5 textboxes. The focus does indeed work "the
first time" but if the user, after completing his input into the first
box, checks another..... the focus briefly appears in the second
textbox then gains focus on some obscure cell. But repeat this
operation, which clears the box as intended, and click again and focus
is within the second box? It is if you can't switch focus from one
textbox to another even if prior to running the main code you oblige
it's idiosyncrasies by placing the focus on a cell.
Although there are more Checkboxes then textboxes it would be rare to
need to tick them all.
Indeed some already have a tick to show a compliance. This seemed the
easiest way to stear the user into dialog if he altered the default
state of the Checkboxes.
Hope this all makes sense.