Combobox-like functionality without forms or oleobjects?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 17 Sep 2007 13:18 GMT

Hello,

I was wondering if it were possible to restrict the values that can be
entered into a cell (which I know can be done) but to allow for the
available options to be displayed when the user has the cell selected
(a-la combobox) without adding a new form to my worksheet or inserting
the oleobject "ComboBox"

Basically, I want the user to select a cell and have a chocie between
three strings

"Better"
"Same"
"Worse"

Thanks in advance

Reply to this Message

papou - 17 Sep 2007 13:31 GMT

Hello Chris
See Data->Validation->Allow->List

HTH
Cordially
Pascal

> Hello,
>
[quoted text clipped - 12 lines]
>
> Thanks in advance

Reply to this Message

Chris - 17 Sep 2007 13:54 GMT

Awesome!

That works exactly how I want, however, when I try to put it into VBA
(having taken the recorded code) I get the following error

Application-defined or object-defined error

My Code:

On Error GoTo ErrorHandler
With Range("A1").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=$M$11:$M$13"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ErrorHandler:
MsgBox ("Error - " & Err.Description)
End Sub

I've tried specifying Selection.Validation and also
Cells(1,1).Validation but it appears the code craps out on the

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=Results!$M$11:Results!$M$13"

Any insight?

Thanks in advance

> Hello Chris
> See Data->Validation->Allow->List
[quoted text clipped - 22 lines]
>
> > Thanks in advance

Reply to this Message

papou - 17 Sep 2007 14:09 GMT

Chris
You can't refer to another sheet, the workaround is to name your range and
use this name for the source range eg:
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,Operator:= _
xlBetween, Formula1:="=MyList"

Where "MyList" will refer to =Results!$M$11:$M$13

HTH
Cordially
Pascal

Awesome!

That works exactly how I want, however, when I try to put it into VBA
(having taken the recorded code) I get the following error

Application-defined or object-defined error

My Code:

On Error GoTo ErrorHandler
With Range("A1").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=$M$11:$M$13"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ErrorHandler:
MsgBox ("Error - " & Err.Description)
End Sub

I've tried specifying Selection.Validation and also
Cells(1,1).Validation but it appears the code craps out on the

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=Results!$M$11:Results!$M$13"

Any insight?

Thanks in advance

On Sep 17, 8:32 am, "papou" <cpapoupasbon@çanonpluslaposte.net> wrote:

> Hello Chris
> See Data->Validation->Allow->List
[quoted text clipped - 22 lines]
>
> > Thanks in advance

Reply to this Message

Chris - 17 Sep 2007 14:39 GMT

I've tried that and it still gives the same error

Any other suggestions?

> Chris
> You can't refer to another sheet, the workaround is to name your range and
[quoted text clipped - 76 lines]
>
> > > Thanks in advance

Reply to this Message

Chris - 17 Sep 2007 15:01 GMT

Ok, I've re-recorded the script and it works if I just run within the
VB editor for excel, however this isn't what I want.

I have a button and I want the validation to be placed on a cell when
the button is clicked. I have it in the click event handler for the
button.

So in summary

Run from IDE - Works
Run from Button Click - Doesn't work

> I've tried that and it still gives the same error
>
[quoted text clipped - 80 lines]
>
> > > > Thanks in advance

Reply to this Message

Chris - 17 Sep 2007 15:21 GMT

Found the answer on another forum...

http://www.tek-tips.com/viewthread.cfm?qid=953193&page=7
-------------------------------
Helpful Member!xlbo (MIS)
18 Nov 04 11:50
Have replied via email - seems that th eissue was caused by the code
being run from a commandbutton that had its "TakeFocusOnClick"
property set to true rather than false - the issue was with the active
object being the commandbutton rather than a sheet
-------------------------------

Once I changed that property for the button it worked...

Hooray!

> Ok, I've re-recorded the script and it works if I just run within the
> VB editor for excel, however this isn't what I want.
[quoted text clipped - 92 lines]
>
> > > > > Thanks in advance

Reply to this Message

papou - 18 Sep 2007 07:23 GMT

Hi Chris
You should have mentioned this before.
The TakeFocusOnClick property is only available for commandbuttons from the
controls Tools Box and NOT the forms Tools box.
When this property is set to True, once you have clicked on the control,
focus will remain on the button and thus will raise errors if you attempt
any operation into the worksheet via code.

HTH
Cordially
Pascal

Ok, I've re-recorded the script and it works if I just run within the
VB editor for excel, however this isn't what I want.

I have a button and I want the validation to be placed on a cell when
the button is clicked. I have it in the click event handler for the
button.

So in summary

Run from IDE - Works
Run from Button Click - Doesn't work

> I've tried that and it still gives the same error
>
[quoted text clipped - 85 lines]
>
> > > > Thanks in advance

Reply to this Message