Is there a way to have an ActiveX ComboBox's LinkedCell output as a
number and not text ?


- Ronald K.

kittronald pretended :
Is there a way to have an ActiveX ComboBox's LinkedCell output as a
number and not text ?


- Ronald K.

A combobox is a dropdown textbox, and so the answer to your Q is 'No'.
You can, however, convert the value to the appropriate numeric type if
IsNumeric(ComboBox1.Text)...

With ComboBox1
If IsNumeric(.Text) Then
CLng(.Text) 'convert to Long
CInt(.Text) 'convert to Integer
CDbl(.Text) 'convert to Double
'...
End If 'IsNumeric(.Text)
End With 'ComboBox1

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

After inserting a module with the code, the VBE opens and selects "With
ComboBox1" with the error below:

Compile error:

Invalid outside procedure

The combobox is named ComboBox1.



- Ronald K.

kittronald formulated the question :
Garry,

After inserting a module with the code, the VBE opens and selects "With
ComboBox1" with the error below:

Compile error:

Invalid outside procedure

The combobox is named ComboBox1.



- Ronald K.

Well, the code should go behind the class that contains the combobox.
This would be a userform or a worksheet. Otherwise, if you want to ref
the combobox in a standard module then you need to prepend its parent
object.

Examples:

With UserForm1.ComboBox1...
or
With Sheets("sheetname"[or index]).ComboBox1...


If the code resides in the combobox's parent class:

With Me.ComboBox1...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

After inserting the code in its own module, I've tried prepending with:

Forms.ComboBox.1

This is what displays in the formula box when I select the
ActiveX ComboBox

Sheet2.ComboBox1

Me.ComboBox1

Sheets("Settings").ComboBox1

None of these change the format of the linked cell.

Does it matter if this an ActiveX Combobox instead of a forms Combobox ?



- Ronald K.

kittronald explained :
Garry,

After inserting the code in its own module, I've tried prepending with:

Forms.ComboBox.1

This is what displays in the formula box when I select the
ActiveX ComboBox

Sheet2.ComboBox1

Me.ComboBox1

Sheets("Settings").ComboBox1

None of these change the format of the linked cell.

Does it matter if this an ActiveX Combobox instead of a forms Combobox ?



- Ronald K.

I don't understand why you're trying to change the format of its
LinkedCell. I thought you wanted to use its Text as a numeric value,
and so is why I provided examples of how to convert 'text as numbers'
to actual numeric values.

Also, to ref the combobox's LinkedCell property you need to append
'.LinkedCell' to it in code.

Example:
Me.ComboBox1.LinkedCell = Me.Range("$C$1").Address

To set ComboBox1.List:
Me.ComboBox1.ListFillRange = Me.Range("$A$1:$A$5").Address

These examples assume the code is behind the worksheet the combobox is
on. (Which makes that sheet the combobox's 'Parent')

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

I have an ActiveX ComboBox named ComboBox1 on Sheet2.

The ListFillRange property is set to a named range called "Days_List"
containing numbers only (i.e., 10, 20, 30, etc.).

The LinkedCell property is set to the name "Days".

Both the "Days" and "Days_List" names reside on Sheet2.

I'm trying to get the LinkedCell to be output as a number.

When I input the code:

With Sheet2.ComboBox1.LinkedCell = .Range("Days").Address
If IsNumeric(.Text) Then
CLng(.Text)
CInt(.Text)
CDbl(.Text)
End If
End With


... the error message below displays:

Compile error:

Invalid outside procedure


Could you tell me what I'm doing wrong ?


- Ronald K.

kittronald explained on 8/10/2011 :
Garry,

I have an ActiveX ComboBox named ComboBox1 on Sheet2.

The ListFillRange property is set to a named range called "Days_List"
containing numbers only (i.e., 10, 20, 30, etc.).

The LinkedCell property is set to the name "Days".

Both the "Days" and "Days_List" names reside on Sheet2.

I'm trying to get the LinkedCell to be output as a number.

When I input the code:

With Sheet2.ComboBox1.LinkedCell = .Range("Days").Address
If IsNumeric(.Text) Then
CLng(.Text)
CInt(.Text)
CDbl(.Text)
End If
End With


... the error message below displays:

Compile error:

Invalid outside procedure


Could you tell me what I'm doing wrong ?


- Ronald K.

With Sheet2.ComboBox1
If IsNumeric(.Text) Then
CLng(.Text)
CInt(.Text)
CDbl(.Text)
End If
End With

So, scrap the LinkedCell property and use an event in the code module
behind Sheet2. (Right-click the sheet tab and choose 'View Code')

Option Explicit

Private Sub ComboBox1_Change()
Me.Range("Days").Value = CLng(Me.ComboBox1.Text)
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

After using the updated code, when selecting a value in ComboBox1, the
VBE highlights the line "With Sheet2.ComboBox1" and displays the following
error:

Compile error:

Invalid outside procedure



- Ronald K.

kittronald wrote :
Garry,

After using the updated code, when selecting a value in ComboBox1, the
VBE highlights the line "With Sheet2.ComboBox1" and displays the following
error:

Compile error:

Invalid outside procedure



- Ronald K.

Yeah, I wasn't trying to give you functional code so much as I was
trying to help you to understand the 'concept' of the code.

Replace: 'With Sheet2.ComboBox1'

...with: 'With Sheets("Sheet2'sNameGoesHere").ComboBox1'

I apologize for not spelling things out more explicitly!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

Thanks for hanging in there.

Same error.

If Sheet2 has a name of "Settings", shouldn't Sheet2.ComboBox1 be the
same as Sheets("Settings").ComboBox1 ?

Also, the CLng, CInt and CDbl lines cause the following error to
display:

Compile error:

Expected: identifier


- Ronald K.

It happens that kittronald formulated :
Garry,

Thanks for hanging in there.

Same error.

If Sheet2 has a name of "Settings", shouldn't Sheet2.ComboBox1 be the
same as Sheets("Settings").ComboBox1 ?

Also, the CLng, CInt and CDbl lines cause the following error to display:

Compile error:

Expected: identifier


- Ronald K.

Ok, if you're not able to get a qualified reference to the sheet where
the combobox is, OR the combobox itself, then you're also not going to
return any text values for converting to another data type. VBA will
raise an error in these cases. Question is why aren't you able to get a
qualified ref to the combobox and its text. I'd be able to better help
you if you post the ENTIRE procedure code that's raising these
exceptions.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Ronald,
Here's an exercise you can try to help you understand how to work with
and manipulate a combobox (or any other control) you create on a
worksheet.

Open a new workbook and on Sheet1 create a combobox over cells C3:D4.

Create a list of numeric values (10,20,30,40,50) in A1:A5.

While in DesignMode:
Right-click the combobox;
Open its Properties window from the popup menu;
Set its 'ListFillRange' to A1:A5, close the window, exit DesignMode.

Right-click the sheet tab and open the code module behind Sheet1.
Maximize the window so it fills the code pane.

In the left side dropdown at the top of the code window, select
ComboBox1. This will create its _Change event sub. Enter the code I
posted earlier for this event.

Normalize the VBE window so you can see the worksheet behind it.

Make sure the Immediate Window is open below the code window. (Hit
Ctrl+G)

In the Immediate Window:

Type...
Sheet1.ComboBox1.Text="20"
Observe what happens on the worksheet.

Type...
Sheets("Sheet1").ComboBox1.Text="30"
Observe what happens on the worksheet.

In the worksheet:
Select a value from the combobox dropdown
Observe what happens on the worksheet.

Back in the VBE:
Insert a module into the workbook, and paste the follow procedure into
its code window.

Option Explicit

Sub TestCombobox()
Sheet1.ComboBox1.Text = "50"
Sheets("Sheet1").ComboBox1.Text = "20"
End Sub

Use F8 to step through the code line by line, observing what happens in
the worksheet as you go.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Just so there's no ambiguity as to what to do with the ComboBox1_Change
event code, just paste the following into the code window the Sheet1.

Private Sub ComboBox1_Change()
Me.Range("C1") = CLng(Me.ComboBox1.Text)
End Sub

This will obviate the need for you to define a named range for the
results.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry,

Wow, thanks for the support - it works now !

From your examples, I've learned the following:

1) Sheet2.ComboBox1 is the same as Sheets("Settings").ComboBox1

2) Forms control code resides on a specific worksheet instead of
globally in the workbook

3) The code creates an event, that upon selecting a value from the
drop down list, formats the output as a number

4) The second line replaces the need to set the LinkedCell property

Thanks again !


- Ronald K.

kittronald expressed precisely :
Garry,

Wow, thanks for the support - it works now !

From your examples, I've learned the following:

1) Sheet2.ComboBox1 is the same as Sheets("Settings").ComboBox1

2) Forms control code resides on a specific worksheet instead of
globally in the workbook

3) The code creates an event, that upon selecting a value from the
drop down list, formats the output as a number

4) The second line replaces the need to set the LinkedCell property

Thanks again !


- Ronald K.

Congrats! ..glad I was able to be of help. Thanks for the feedback...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc