Combobox-like functionality without forms or oleobjects?

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

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
 
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
 
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

"Chris" <[email protected]> a écrit dans le message de (e-mail address removed)...
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
 
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
 
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
 
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


"Chris" <[email protected]> a écrit dans le message de (e-mail address removed)...
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
 
Back
Top