Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird Error with ComboBox_Change()
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird Error with ComboBox_Change()
.controls("labelG16").Caption = _
FormatNumber((Worksheets("Worksheet1") _ .Cells(16,7).Value), 2) -- Regards, Tom Ogilvy "Justin" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird Error with ComboBox_Change()
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 wrote: .controls("labelG16").Caption = _ FormatNumber((Worksheets("Worksheet1") _ .Cells(16,7).Value), 2) -- Regards, Tom Ogilvy "Justin" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird Error with ComboBox_Change()
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 -- Regards, Tom Ogilvy "Justin" wrote: 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 wrote: .controls("labelG16").Caption = _ FormatNumber((Worksheets("Worksheet1") _ .Cells(16,7).Value), 2) -- Regards, Tom Ogilvy "Justin" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird Error with ComboBox_Change()
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) Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy "Justin" wrote: 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 wrote: .controls("labelG16").Caption = _ FormatNumber((Worksheets("Worksheet1") _ .Cells(16,7).Value), 2) -- Regards, Tom Ogilvy "Justin" wrote: 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! -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Weird Error with ComboBox_Change()
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! Dave Peterson wrote: 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) Tom Ogilvy wrote: 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 -- Regards, Tom Ogilvy "Justin" wrote: 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 wrote: .controls("labelG16").Caption = _ FormatNumber((Worksheets("Worksheet1") _ .Cells(16,7).Value), 2) -- Regards, Tom Ogilvy "Justin" wrote: 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! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weird error | Excel Programming | |||
Very weird error | Excel Worksheet Functions | |||
Combobox_change does not repond? | Excel Programming | |||
Weird error | Excel Programming | |||
ComboBox_Change sub runs unexpectedly | Excel Programming |