Combobox-like functionality without forms or oleobjects?

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
 
C

Chris

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
 
P

papou

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 [email protected]...
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
 
C

Chris

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
 
C

Chris

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
 
P

papou

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 [email protected]...
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
 
Top