helppp, excel worksheet...

[pangolin_10]

hi everybody,
i have a form in which i have imported an excel worksheet. what i want to do is that each cell of the worksheet will have a combo-box like property,i.e. a edit part and a list part. can this be done by using macros? if so, how? and if not, then how do i do this? then i need the combo box to behave like- when the mouse pointer is moved over any cell of the worksheet, the drop-down property of the combo box in thet cell will be activated. plzzz helpppp.

[Pinky98]

You are best off using the Office web components, and placing an excel sheet onto your form.

Then, rather than using macros (which you can do, if you like)... it is easier to place a "validation" on the cell. Only allow certain values in the cell, and thell excel to display the cell as a drop down box.

[Shuja Ali]

hi everybody,
i have a form in which i have imported an excel worksheet. what i want to do is that each cell of the worksheet will have a combo-box like property,i.e. a edit part and a list part. can this be done by using macros? if so, how? and if not, then how do i do this? then i need the combo box to behave like- when the mouse pointer is moved over any cell of the worksheet, the drop-down property of the combo box in thet cell will be activated. plzzz helpppp.

Have you tried checking the Menus in Excel. There is a MenuItem under Data-->Validation where you can make a Cell/Column act like a combobox. No code neds to be written for that. You just need set the values you want to be in the List. Try going to Data-->Validation and in the resulting dialog select List for Allow: Field and enter the List Items in the Source field.
and once this is done, whenever you select that cell a Drowdown button will appear at the corner which you can click to bring up the List to select items.

[doofusboy]

Select the cell where you want the drop down to appear and then add this code:

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$B$1:$B$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Make A Selection"
.ErrorTitle = "Nice Work Genius"
.InputMessage = "Select"
.ErrorMessage = "You messed up"
.ShowInput = True
.ShowError = True
End With


Where cells B1 through B10 contain the values you want in a drop down list.

[Shuja Ali]

Select the cell where you want the drop down to appear and then add this code:

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$B$1:$B$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Make A Selection"
.ErrorTitle = "Nice Work Genius"
.InputMessage = "Select"
.ErrorMessage = "You messed up"
.ShowInput = True
.ShowError = True
End With


Where cells B1 through B10 contain the values you want in a drop down list.

Why write a macro for it when you could just do it with couple of Clicks?
Refer my previous post, I am doing the same thing using the Data Menu.

[Pinky98]

Why write a macro for it when you could just do it with couple of Clicks?

Probably because the original post asked about macros?? But, yeah... no need for it.

[Shuja Ali]

Probably because the original post asked about macros?? But, yeah... no need for it.

We should try to give a better solution always rather than just following OP's question. If there is a better way of solving a problem then we should follow that. Ain't it?

[pangolin_10]

thanx evrybdy, all of u have helped me a lot. both the macro definition and the simple excel feature described by u did the trick. thnx again

[Pinky98]

Absolutely Shuja