Combobox_change does not repond?

[Sige]

Hi There,

I load a combobox with my fonts but would like it to update my range
"text" upon changing it ...

When I "run" the sub it works!!! ... but not when I just change my
selection.

Private Sub ComboBox1_Change()
On Error Resume Next
ThisWorkbook.Sheets("Fonts").Range("Text").Font.Name =
ComboBox1.Value
End Sub

Anyone?

Brgds Sige
XL97-Sr2

 

[NickHK]

Sige,
I would assume that as you are not Changing the combo box text (typing in
the edit portion).
Try the Click event instead.

NickHK

 

[Sige]

Hi Nick,
Thanks for the advice ... I tried Click & DropButtonClick.
But with the same result!

(Though, here again: When running the "sub" explicitely it works like a
charm)

Any other possible reason?

BRGDS Sige



Private Sub ComboBox1_Click()
On Error Resume Next
Sheets("Fonts").Range("Text").Font.Name = ComboBox1.Value
End Sub

Private Sub ComboBox1_DropButtonClick()
On Error Resume Next
Sheets("Fonts").Range("Text").Font.Name = ComboBox1.Value
End Sub

 

[NickHK]

Sige,
Are you using the combo box from the Forms or Controls tool box ?

NickHK

 

[Sige]

Control Toolbox ... (

 

[Norman Jones]

Hi Sige,

Your code worked for me providing the parent workbook contained a worksheet
named 'Fonts' and that this sheet had a range named 'Text'.

Try removing the On Error Resume next line, which may be making your
problem, and verify that the sheet and range names correspond *exactly* -
look especially for leading or trailing spaces in the names.

 

[Norman Jones]

Hi Sige,

Try removing the On Error Resume next line, which may be making

Should be:

Try removing the On Error Resume next line, which may be MASKING

 

[Sige]

Removed the On Error resume next ...
"Unable to set the property of the Font class"


Sub FillFontBox()
Dim FontList As CommandBarComboBox
Dim i As Long
Dim tempbar As CommandBar
' On Error Resume Next
Set FontList =
Application.CommandBars("Formatting").FindControl(Id:=1728)

If FontList Is Nothing Then
Set tempbar = Application.CommandBars.Add
Set FontList = tempbar.Controls.Add(Id:=1728)
End If

On Error GoTo 0
With Sheets(1)
.Range("A1").Activate
.ComboBox1.Clear
For i = 0 To FontList.ListCount - 1
.ComboBox1.AddItem FontList.List(i + 1)
Next i
.ComboBox1.Value = "Arial"

End With
' Delete temp CommandBar if it exists
On Error Resume Next
tempbar.Delete
End Sub

I don't have more code than this in my wbk....
Oh lalala, let's go excelling
Sige

 

[Sige]

1. I would like to load a combo ...with the fonts on my system
2. Upon clicking I would like to change the Text in a certain range
with the Font that just has been selected.

I tried it all but this easy task does not work ...

1. Fillfontbox opened on workbook_open (Run "FillFontBox"; or Call
FillFontBox)... seems to create a problem already for finding the
list!!
2. When explicitely running the sub again...the combo gets filled ...
but then on click nothing happens. Running "Private Sub
ComboBox1_DropButtonClick()" explicitely again, changes my Text-range
in the font just selected.

Anyone with a bright Idea?

Running XL97Sr2

Please please please Sige

 

[Norman Jones]

Hi Sige,

Your code works for me without any problem.

If you wish, send me a zipped workbook and I will endeavour to resolve.


norman_jones@NOSPAMbtconnectDOTcom


(Remove "NOSPAM" and replace "DOT" )

 

[Sige]

Ladies & Gentlemen,

When Using !!! XL97 !!!

Hi Sige,

Try changing:

Private Sub ComboBox1_DropButtonClick()
On Error Resume Next
Sheets("Fonts").Range("Text").Font.Name = ComboBox1.Value
End Sub

To:

Private Sub ComboBox1_DropButtonClick()
ActiveCell.Activate
Sheets("Fonts").Range("Text").Font.Name = ComboBox1.Value
End Sub

---
Regards,
Norman

Eternal gratitude for Norman!
Tack, tack, tusen tack!
Sige