Need help with formatting

[Guest]

Oh Wise Ones,

I have some code to double-click through some selections in a cell. The
problem is the cell won't retain the formatting I have set. The selection
event supersedes the format. So it looks like I have to format in in the code
and I'm not sure of the syntax. I need the selection formatted 16 point and
Bold. Any help would be appreciated. The code is below:

Option Explicit
Private mcolFasteners As Collection

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
'Changes cell B2 value among
'"Royal Sovereign, Sentinel, Hip and Ridge" on double-click.

'Limit the effect to cell B2.
If Target.Address <> Range("B2").Address Then
Cancel = False
Exit Sub
End If

On Error GoTo ErrHandler
Dim strBuf As String, v As Variant
'Instatiate and populate a collection
'if not already done.
If mcolFasteners Is Nothing Then
Set mcolFasteners = New Collection
With mcolFasteners
.Add "Royal Sovereign", "Sentinel"
.Add "Hip & Ridge", "Royal Sovereign"
.Add "Sentinel", "Hip & Ridge"
End With
End If

'Get, then clear the current value.
strBuf = LCase(Trim(Target.Text))
Target.Clear

'If it is in our collection, use it as
'an index to the next item in the collection.
For Each v In mcolFasteners
If LCase(CStr(v)) = strBuf Then
Target.Value = mcolFasteners(v)
Exit For
End If
Next v

'If not, just assign the cell one of the values.
If Len(Target.Value) < 1 Then
Target.Value = mcolFasteners(1)
End If

ErrHandler:
Cancel = True

End Sub

 

[Jim Cone]

Target.Clear 'removes formatting
Target.ClearContents 'does not remove formatting
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Mike K"
<[email protected]>
wrote in message
Oh Wise Ones,
I have some code to double-click through some selections in a cell. The
problem is the cell won't retain the formatting I have set. The selection
event supersedes the format. So it looks like I have to format in in the code
and I'm not sure of the syntax. I need the selection formatted 16 point and
Bold. Any help would be appreciated. The code is below:

Option Explicit
Private mcolFasteners As Collection

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
'Changes cell B2 value among
'"Royal Sovereign, Sentinel, Hip and Ridge" on double-click.

'Limit the effect to cell B2.
If Target.Address <> Range("B2").Address Then
Cancel = False
Exit Sub
End If

On Error GoTo ErrHandler
Dim strBuf As String, v As Variant
'Instatiate and populate a collection
'if not already done.
If mcolFasteners Is Nothing Then
Set mcolFasteners = New Collection
With mcolFasteners
.Add "Royal Sovereign", "Sentinel"
.Add "Hip & Ridge", "Royal Sovereign"
.Add "Sentinel", "Hip & Ridge"
End With
End If

'Get, then clear the current value.
strBuf = LCase(Trim(Target.Text))
Target.Clear

'If it is in our collection, use it as
'an index to the next item in the collection.
For Each v In mcolFasteners
If LCase(CStr(v)) = strBuf Then
Target.Value = mcolFasteners(v)
Exit For
End If
Next v

'If not, just assign the cell one of the values.
If Len(Target.Value) < 1 Then
Target.Value = mcolFasteners(1)
End If

ErrHandler:
Cancel = True

End Sub

 

[Guest]

Try this modified section of your code.

For Each v In mcolFasteners
If LCase(CStr(v)) = strBuf Then
Target.Value = mcolFasteners(v)
Target.Font.Size = 16
Target.Font.Bold = True
Exit For
End If
Next v

 

[Guest]

Jim,
Thank you kind sir! That did the trick.

Mike

 

[Guest]

Thanks for the response. But apparently the Target.Clear was wiping my
formatting. Target.ClearContents left the formatting intact. The little
things....

Mike