Weird Error with ComboBox_Change()

[Justin]

I have a UserForm with MultiPages, ComboBoxes, and Captions.

I have it set up right now so that when I select a new value for the
ComboBox, the ComboBox_Change() runs a Caption update to format the new
value of a cell in that caption with the proper number of decimals (2).

Here is the code that I have for the ComboBox_Change():


Private Sub ComboBox1_Change()

With UserForm1.MultiPage1.Pages(0).MultiPage2.Pages(1)

Worksheets("Worksheet1").Cells(13, 5).Value = .ComboBox1.Value

.labelG16.Caption = FormatNumber((Worksheets("Worksheet1").Cells(16,
7).Value), 2)

End With

End Sub


The Error I get when this runs is: "Error "438": Object doesn't support
this property or method." When I hit "debug" it sends me to the
..labelG16.Caption line.

Does anybody have any ideas with this? I really appreciate the help!

 

[Tom Ogilvy]

.controls("labelG16").Caption = _
FormatNumber((Worksheets("Worksheet1") _
.Cells(16,7).Value), 2)

 

[Justin]

Thanks - That works now for my other cells, but for some reason it
doesn't like the 1 cell I'm linking to... the cell contains a large IF
statement and is now giving me the "Run-Time Error 13: Type Mismatch"
error. I tried linking that Caption to other cells on the worksheet
and it works fine. So it's not the code, it's something to do with the
Cell in the Worksheet. I have tried making it a Number formatting,
General Formatting, etc... that doesn't change anything.

 

[Tom Ogilvy]

set rng = Worksheets("Worksheet1") _
.Cells(16,7).Value
if isnumeric(rng) then
.controls("labelG16").Caption = _
FormatNumber(rng.Value, 2)
else
msgbox rng.Address & " doesn't contain a number"
end if


to illustrate from the immediate window:

? formatnumber("ABC",2) '<-- raises type mismatch error

? formatNumber("123.34567",2)
123.35

 

[Dave Peterson]

Just a typo alert:

set rng = Worksheets("Worksheet1") _
.Cells(16,7).Value
should be
set rng = Worksheets("Worksheet1") _
.Cells(16,7)
(w/o the .value)

 

[Justin]

Thanks guys! With your help I solved the problem - the cell (for
whatever reason) was not a numeric value during the INITIAL "change" of
the combobox, which was causing the error.

I used the "If isnumeric" statement to cancel the Caption formatting if
it wasn't numeric, and it works fine after I open the page. Kinda
confusing to describe, but just wanted to thank both of you for your
help! Much appreciated!