Sheet combobox help

I

IT_roofer

I have a combobox on a sheet that I need to assign values (say from 200, 210,
220...300) and when you choose a value from it, it multiplies that value with
the value from a cell. I have tried loading these values with the on activate
and worksheet open etc, but I couldn't get it to load values until I assigned
the input range. Now it loads the values, but I have the text on the sheet
(hidden by matching text color with cell background color) and I would like
to not go that route if possible. Anyway, I created a new macro to multiply:
Sheet2.Range("T44").Value = Sheet2.Range("r44").Value * Sheet2combo.text -
but it doesn't work..

Can someone help? Thanks!
 
R

Ronald Dodge

This is a question that's better suited in the
microsoft.public.excel.programming newsgroup. I do not know how much of VBA
you know as far as working on the VBA side with the various things in it as
well as the various objects, collections and properties that you can gain
access to and how the structure of such items are in Excel VBA.

For the combo box, though not sure which of the 2 you using as there are 2
different sets of controls within Excel, it is located within the "Shapes"
collection that is an object of the worksheet that the object resides on.
You have the Form's Toolbar, and you also have the Toolbox button within the
Visual Basic toolbar, which there are some differences between the 2 sets of
objects. Unlike in Access that uses either the Text or Caption property
(depending on the type of control it is) of such controls to retrieve how it
appears to the user, use the Value property of the object in Excel to
retrieve that data, when it's directly on a worksheet.

VBA ComboBox FillRange:

Workbooks(<WorkbookName>).Worksheets(<WorksheetName>).Shapes(<ObjectItemIndexOrName>).DrawingObject.ListFillRange


VBA ComboBox Value

Workbooks(<WorkbookName>).Worksheets(<WorksheetName>).Shapes(<ObjectItemIndexOrName>).DrawingObject.Object.Value


Forms ComboBox FillRange

Workbooks(<WorkbookName>).Worksheets(<WorksheetName>).Shapes(<ObjectItemIndexOrName>).FormatControl.ListFillRange


Forms ComboBox Value

Not really sure where to get this value from the VBA side as I'm not having
much luck with the example that I'm using to test this out.



Anyhow, I generally use the VBA controls rather than the Forms controls
cause I can get to the methods and properties much more easily with the VBA
controls than I can with the Forms controls and also can control how they
look more easily too. Many prefer to use the Forms controls cause assigning
macros is much more intuitive than it is with the VBA controls, but if you
know how to work with VBA, it's just as easy to assign macros to the VBA
controls (via View Code option) as it is with Forms controls (via Assign
Macro... option). With the View Code option, you type in the name of the
macro (may want to prequalify it with the workbook's VBProjects code name,
if you have modified this value for such purpose, which also helps avoid
naming conflicts) while with the Assign Macro option, it's click and point
for assigning the macro.

Ronald R. Dodge, Jr.
Master MOUS 2000
 
I

IT_roofer

Mr Dodge - Thanks for your reply. You hit the jackpot - I didn't realize
there were two different sets of objects and when I switched to the other
type I was able to make the control do exactly what I needed it to do. I
wouldn't say I am an expert with VBA, however, the control I was using first
wasn't liking the vba I was trying to attach, but the second did. Thank you
very much; you saved the day!
 
Top