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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Sige]

Control Toolbox ... :o(

[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.

---
Regards,
Norman



"Sige" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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

---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> ---
> Regards,
> Norman
>
>
>
> "Sige" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

[Sige]

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

>>>Am I not loading the Combo properly? .... It is an adjustment from J-Walk The Man!


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



Sige wrote:
> Removed the On Error resume next ...
> "Unable to set the property of the Font class"
>
> >>>Am I not loading the Combo properly? .... It is an adjustment from J-Walk The Man!
>
>
> 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

[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" )


---
Regards,
Norman



"Sige" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
> Sige wrote:
>> Removed the On Error resume next ...
>> "Unable to set the property of the Font class"
>>
>> >>>Am I not loading the Combo properly? .... It is an adjustment from
>> >>>J-Walk The Man!
>>
>>
>> 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]

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

Norman Jones wrote:
> 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" )
>
>
> ---
> Regards,
> Norman
>
>
>
> "Sige" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
> > Sige wrote:
> >> Removed the On Error resume next ...
> >> "Unable to set the property of the Font class"
> >>
> >> >>>Am I not loading the Combo properly? .... It is an adjustment from
> >> >>>J-Walk The Man!
> >>
> >>
> >> 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
> >