ExcelBanter - Newbie question

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Newbie question (https://www.excelbanter.com/excel-programming/364006-newbie-question.html)

OK, I have done VBA programming in AutoCAD and now I am venturing int
Excel.

I tried a search but I may not have picked the right keywords and
found nothing... so don't flame me too hard.

I see that you can insert control objects into an Excel spreadsheet
When you right click on the control and go to Combobox Object and the
Edit, it takes you the VBA editor. I do not see initialize. I merel
want to populate the pulldown with noneditable values.

Any good tutorials on this? I can't stand microsoft help. haha.

Thanks again, all

--
Barry Clar
-----------------------------------------------------------------------
Barry Clark's Profile: http://www.excelforum.com/member.php...fo&userid=3526
View this thread: http://www.excelforum.com/showthread.php?threadid=55102

I use the userform initialize to get the values loaded into the combobox.
You can also set a rowsource to a range in excel...but you said not editable.

Private Sub UserForm_Initialize()
If Range("MotorShaftValue").Value = "None" Then
CmbxLineShaft.AddItem "316 SS"
CmbxLineShaft.AddItem "416 SS"
Else
CmbxLineShaft.AddItem "Yes"
CmbxLineShaft.AddItem "No"
End If
End Sub

HTH

"Barry Clark" wrote:

OK, I have done VBA programming in AutoCAD and now I am venturing into
Excel.

I tried a search but I may not have picked the right keywords and I
found nothing... so don't flame me too hard.

I see that you can insert control objects into an Excel spreadsheet.
When you right click on the control and go to Combobox Object and then
Edit, it takes you the VBA editor. I do not see initialize. I merely
want to populate the pulldown with noneditable values.

Any good tutorials on this? I can't stand microsoft help. haha.

Thanks again, all.

--
Barry Clark
------------------------------------------------------------------------
Barry Clark's Profile: http://www.excelforum.com/member.php...o&userid=35267
View this thread: http://www.excelforum.com/showthread...hreadid=551024

Assuming controls from the control toolbox toolbar:

A control on a worksheet doesn't have an initialize event. (or on a
userform, but a userform has such an event).

A combobox can not have a static list - you either need to link/bind it to a
range of cells (using the property ListFillRange), or you need to populate it
with code. This code could be triggered by one of the other events such as
the worksheet activate if that will be fired before you need to use the
combobox.

While controls operate somewhat differently on worksheets, this gives you a
general introduction to their functionality:

http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

Microsoft(R) Visual Basic(R) for Applications Examples for Controlling
UserForms in Microsoft Excel 97

This Application Note is an introduction to manipulating UserForms in
Microsoft Excel 97. It includes examples and Microsoft Visual Basic for
Applications macros that show you how to take advantage of the capabilities
of UserForms and use each of the ActiveX controls that are available for
UserForms
--------------------
http://j-walk.com/ss/excel/tips/tip84.htm

See this tutorial here
http://www.dicks-blog.com/excel/2004...g_userfor.html

http://support.microsoft.com/default...b;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel

--
Regards,
Tom Ogilvy

"Barry Clark" wrote:

OK, I have done VBA programming in AutoCAD and now I am venturing into
Excel.

I tried a search but I may not have picked the right keywords and I
found nothing... so don't flame me too hard.

I see that you can insert control objects into an Excel spreadsheet.
When you right click on the control and go to Combobox Object and then
Edit, it takes you the VBA editor. I do not see initialize. I merely
want to populate the pulldown with noneditable values.

Any good tutorials on this? I can't stand microsoft help. haha.

Thanks again, all.

--
Barry Clark
------------------------------------------------------------------------
Barry Clark's Profile: http://www.excelforum.com/member.php...o&userid=35267
View this thread: http://www.excelforum.com/showthread...hreadid=551024

The easiest way is to create a range of values in a worksheet, and set the
ListFillRange property of the combo to that range. When you add the combo,
double click it that will take you into the VBIDE where you can set the
ListFillRange property.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Barry Clark"
wrote in message
...

OK, I have done VBA programming in AutoCAD and now I am venturing into
Excel.

I tried a search but I may not have picked the right keywords and I
found nothing... so don't flame me too hard.

I see that you can insert control objects into an Excel spreadsheet.
When you right click on the control and go to Combobox Object and then
Edit, it takes you the VBA editor. I do not see initialize. I merely
want to populate the pulldown with noneditable values.

Any good tutorials on this? I can't stand microsoft help. haha.

Thanks again, all.

--
Barry Clark
------------------------------------------------------------------------
Barry Clark's Profile:
http://www.excelforum.com/member.php...o&userid=35267
View this thread: http://www.excelforum.com/showthread...hreadid=551024

Hey guy.

That's just the thing, Initialize is not available as an option unles
I have the drop down as part of a userform as opposed to an inserte
control object

--
Barry Clar
-----------------------------------------------------------------------
Barry Clark's Profile: http://www.excelforum.com/member.php...fo&userid=3526
View this thread: http://www.excelforum.com/showthread.php?threadid=55102

I SEE! THANKS!

I am sure I will have other noob questions...stay tuned. haha

--
Barry Clar
-----------------------------------------------------------------------
Barry Clark's Profile: http://www.excelforum.com/member.php...fo&userid=3526
View this thread: http://www.excelforum.com/showthread.php?threadid=55102

Ok. haha.

So, I have two sheets in this workbook that I am playing with.

I put a date, Thursday, December 15, 2005, in Sheet1 and cell A1.

I put a combobox on sheet two.

In the combobox properties, I populate the ListFillRange with 'shee
1'!A1

When I pull the menu down, the date shows properly. When I click it an
the combobox collapses again, a numerical value of 38701 shows up. An
thoughts?

Thank you,

Barr

--
Barry Clar
-----------------------------------------------------------------------
Barry Clark's Profile: http://www.excelforum.com/member.php...fo&userid=3526
View this thread: http://www.excelforum.com/showthread.php?threadid=55102

38701 is the value actually stored in the cell. If you format the cell as
general, you will see this number. It is the number of days since Dec 31,
1899.

To overcome this I usually either

a) use code to load the combobox and populate it with a text string
representing the date
Sub AddData()
For Each cell In Worksheets( _
"Sheet1").Range("A1:A10")
Worksheets("Sheet2").OLEObjects( _
"Combobox1").Object.AddItem cell.Text
Next

End Sub

b) use the click event rather than bind the output to a cell

Private Sub Combobox1_Click()
With worksheets("sheet2")
.Range("B9").Value = Format(Combobox1.Value,"mm/dd/yyyy")
End With
Combobox1.Value = Format(Combobox1.Value,"mm/dd/yyyy")
End sub

--
Regards,
Tom Ogilvy

"Barry Clark" wrote:

Ok. haha.

So, I have two sheets in this workbook that I am playing with.

I put a date, Thursday, December 15, 2005, in Sheet1 and cell A1.

I put a combobox on sheet two.

In the combobox properties, I populate the ListFillRange with 'sheet
1'!A1

When I pull the menu down, the date shows properly. When I click it and
the combobox collapses again, a numerical value of 38701 shows up. Any
thoughts?

Thank you,

Barry

--
Barry Clark
------------------------------------------------------------------------
Barry Clark's Profile: http://www.excelforum.com/member.php...o&userid=35267
View this thread: http://www.excelforum.com/showthread...hreadid=551024

Under which procedure should this be done under

--
Barry Clar
-----------------------------------------------------------------------
Barry Clark's Profile: http://www.excelforum.com/member.php...fo&userid=3526
View this thread: http://www.excelforum.com/showthread.php?threadid=55102

I think you stumped me on that one Barry. I have no idea what you are
asking. Are you asking how to load a combobox dropdown with code? (seems
like we are in a circle)

Are you asking what the click event is?

in design mode, right click on the combobox and select view code.

--
Regards,
Tom Ogilvy

"Barry Clark" wrote:

Under which procedure should this be done under?

--
Barry Clark
------------------------------------------------------------------------
Barry Clark's Profile: http://www.excelforum.com/member.php...o&userid=35267
View this thread: http://www.excelforum.com/showthread...hreadid=551024

You know how when you are in the module in VBA? You choose say combobox
on the left and then on the right you might choose Click, Change or
what-have-you in the procedures box. Which of those might I use as
Initialize isn't there.

--
Barry Clark
------------------------------------------------------------------------
Barry Clark's Profile: http://www.excelforum.com/member.php...o&userid=35267
View this thread: http://www.excelforum.com/showthread...hreadid=551024