Combo box won't transmit "blank"

[sebt]

Hi

I'm using a combo box (from the Control Toolbox) to allow data-entry
into a range of cells.
There's just one combo, which is shifted about to appear on the current
cell, using code adapted from a solution at contextures.com:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim str As String
Dim ComboType As CellCombotype
Dim cboTemp As ComboBox
Dim objThisWorkSheet As Worksheet

Set objThisWorkSheet = ActiveSheet

'These calls returns reference to the combo that should be used for
this particular cell
'(since there is more than one combo)
ComboType = TypeOfCellCombo("Activity Matrix", Target.Row,
Target.Column)
'Display return value for debugging
objThisWorkSheet.Cells(7, 9) = ComboType
Set cboTemp = ComboForCell(ComboType)
If cboTemp Is Nothing Then Exit Sub

With cboTemp
.LinkedCell = ""
.Visible = False
.Value = ""
End With

Application.EnableEvents = False
ActiveCell.Activate
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
'.Font.Name = Target.Font.Name
'.Font.Size = Target.Font.Size
'.Font.Bold = Target.Font.Bold
'.Font.Italic = Target.Font.Italic
.ListWidth = Target.Width + 5
.Height = Target.Height + 5
.LinkedCell = Target.Address
End With
cboTemp.Activate
Application.EnableEvents = True

Exit Sub

The combo is set up to use the Wingdings font, and its ListFillRange is
a range of two cells, one containing "ü" (which appears as a tick in
the Wingdings font) and the other blank.

The first problem is: the combo list shows a tick, and a blank, as
you'd expect. But it's impossible to clear the cell value once a
"tick" has been put in it. Selecting the blank in the combo list makes
the cell appear blank - but as soon as I shift focus to another cell,
it appears that this change hasn't really gone through: the previous
cell still shows a tick. Pressing Delete while in the combo has no
effect either. I've tried changing the "blank" value in the combo's
ListFillRange to ' (single quote), or space - but this has no effect.
Why is the combo not transmitting the "blank" value to the cell?

The second problem, which matters less as there's a workaround, is that
it seems impossible to set the combo's Font properties in VBA. (That's
why the .Font property assignments in the code above are commented
out). Even if the combo is pre-designed with font properties matching
the cell's font properties (i.e. these are set in the combo's
Properties box, rather than VBA), attempting to set _any_ Font property
of the combo in VBA results in it going back to some kind of default
font (Arial?). (Background: I hoped I could get the combo to adjust
its font Size, Bold and Italic at runtime to match the particular cell
it's covering). Code runs without error, but the combo shows the "ü"
instead of the tick, so it must not be using the Wingdings font. Even
stranger, if I go to the Immediate window and ask for

? ActiveSheet.OLEObjects("CboTickOrBlank").Object.Font.Name (or .Size,
or .Bold)

it says "Wingdings", 13, True - while the combo clearly isn't using
this font!
Very strange - can anyone enlighten me? I vaguely remember some
weirdness with the Font object I came across a couple of years ago - do
you have to set up a new Font object or something? (I tried Set
cboTmp.Font=Target.Font, but this gives a type mismatch error)

thanks


Seb

 

[Tom Ogilvy]

I don't have your defined type or your function, so I modified your code not
to rely on them. Adding a doevents after the font part fixed that. the
space/blank worked fine for me (I had a space (char(32)) in the source
cell - I guess that is what you want). In any event, this worked for me.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim str As String
Dim cboTemp As OLEObject
Dim objThisWorkSheet As Worksheet
Dim ws As Worksheet
Set ws = ActiveSheet
Dim sStr As String
Set objThisWorkSheet = ActiveSheet
Set cboTemp = ws.OLEObjects("TempCombo")
If cboTemp Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Column > 2 Then Exit Sub
sStr = "M1:M2"

With cboTemp
.LinkedCell = ""
.Visible = False
.Object.Value = ""

End With

Application.EnableEvents = False
ActiveCell.Activate
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
With .Object
.Font.Name = Target.Font.Name
.Font.Size = Target.Font.Size
.Font.Bold = Target.Font.Bold
.Font.Italic = Target.Font.Italic
.ListWidth = Target.Width + 5
End With
DoEvents
.Height = Target.Height + 5
.ListFillRange = ws.Range(sStr).Address(1, 1, xlA1, True)
.LinkedCell = Target.Address
End With
cboTemp.Activate
Application.EnableEvents = True

Exit Sub

End Sub

--
Regards,
Tom Ogilvy

Hi

I'm using a combo box (from the Control Toolbox) to allow data-entry
into a range of cells.
There's just one combo, which is shifted about to appear on the current
cell, using code adapted from a solution at contextures.com:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim str As String
Dim ComboType As CellCombotype
Dim cboTemp As ComboBox
Dim objThisWorkSheet As Worksheet

Set objThisWorkSheet = ActiveSheet

'These calls returns reference to the combo that should be used for
this particular cell
'(since there is more than one combo)
ComboType = TypeOfCellCombo("Activity Matrix", Target.Row,
Target.Column)
'Display return value for debugging
objThisWorkSheet.Cells(7, 9) = ComboType
Set cboTemp = ComboForCell(ComboType)
If cboTemp Is Nothing Then Exit Sub

With cboTemp
.LinkedCell = ""
.Visible = False
.Value = ""
End With

Application.EnableEvents = False
ActiveCell.Activate
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
'.Font.Name = Target.Font.Name
'.Font.Size = Target.Font.Size
'.Font.Bold = Target.Font.Bold
'.Font.Italic = Target.Font.Italic
.ListWidth = Target.Width + 5
.Height = Target.Height + 5
.LinkedCell = Target.Address
End With
cboTemp.Activate
Application.EnableEvents = True

Exit Sub

The combo is set up to use the Wingdings font, and its ListFillRange is
a range of two cells, one containing "ü" (which appears as a tick in
the Wingdings font) and the other blank.

The first problem is: the combo list shows a tick, and a blank, as
you'd expect. But it's impossible to clear the cell value once a
"tick" has been put in it. Selecting the blank in the combo list makes
the cell appear blank - but as soon as I shift focus to another cell,
it appears that this change hasn't really gone through: the previous
cell still shows a tick. Pressing Delete while in the combo has no
effect either. I've tried changing the "blank" value in the combo's
ListFillRange to ' (single quote), or space - but this has no effect.
Why is the combo not transmitting the "blank" value to the cell?

The second problem, which matters less as there's a workaround, is that
it seems impossible to set the combo's Font properties in VBA. (That's
why the .Font property assignments in the code above are commented
out). Even if the combo is pre-designed with font properties matching
the cell's font properties (i.e. these are set in the combo's
Properties box, rather than VBA), attempting to set _any_ Font property
of the combo in VBA results in it going back to some kind of default
font (Arial?). (Background: I hoped I could get the combo to adjust
its font Size, Bold and Italic at runtime to match the particular cell
it's covering). Code runs without error, but the combo shows the "ü"
instead of the tick, so it must not be using the Wingdings font. Even
stranger, if I go to the Immediate window and ask for

? ActiveSheet.OLEObjects("CboTickOrBlank").Object.Font.Name (or .Size,
or .Bold)

it says "Wingdings", 13, True - while the combo clearly isn't using
this font!
Very strange - can anyone enlighten me? I vaguely remember some
weirdness with the Font object I came across a couple of years ago - do
you have to set up a new Font object or something? (I tried Set
cboTmp.Font=Target.Font, but this gives a type mismatch error)

thanks


Seb

 

[sebt]

Hi Tom

Many thanks for trying this out and posting the results. You've been a
great help, just by saying "this worked for me". Because that made me
take that extra step, thinking, again for the 9th time, "I must have
missed something". And it turned out that, left over from a previous
technique for formatting these cells, the cell's format was set to

ü;ü;ü;ü

The final ü meaning that any text (including the space) will be
formatted as "ü" (which in Wingdings looks like a tick).

I'l just go and beat myself round the head with a rolled-up
newspaper...

thanks again


Seb