Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box won't transmit "blank"
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.Fo nt.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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box won't transmit "blank"
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 "sebt" wrote in message oups.com... 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.Fo nt.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo box won't transmit "blank"
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
If A3=alpha numeric,"X", if A3=text,"Y", Blank | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |