PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Discussion > Combobox-like functionality without forms or oleobjects?

Reply
Thread Tools Rate Thread

Combobox-like functionality without forms or oleobjects?

 
 
Chris
Guest
Posts: n/a
 
      17th Sep 2007
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 With Quote
 
 
 
 
papou
Guest
Posts: n/a
 
      17th Sep 2007
Hello Chris
See Data->Validation->Allow->List

HTH
Cordially
Pascal

"Chris" <(E-Mail Removed)> a écrit dans le message de news:
(E-Mail Removed)...
> 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 With Quote
 
Chris
Guest
Posts: n/a
 
      17th Sep 2007
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
>
> HTH
> Cordially
> Pascal
>
> "Chris" <chris.ole...@gmail.com> a écrit dans le message de news:
> 1190031530.161597.324...@19g2000hsx.googlegroups.com...
>
> > 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 With Quote
 
papou
Guest
Posts: n/a
 
      17th Sep 2007
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" <(E-Mail Removed)> a écrit dans le message de news:
(E-Mail 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

On Sep 17, 8:32 am, "papou" <cpapoupasbon@çanonpluslaposte.net> wrote:
> Hello Chris
> See Data->Validation->Allow->List
>
> HTH
> Cordially
> Pascal
>
> "Chris" <chris.ole...@gmail.com> a écrit dans le message de news:
> 1190031530.161597.324...@19g2000hsx.googlegroups.com...
>
> > 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 With Quote
 
Chris
Guest
Posts: n/a
 
      17th Sep 2007
I've tried that and it still gives the same error

Any other suggestions?

On Sep 17, 9:09 am, "papou" <cpapoupasbon@çanonpluslaposte.net> wrote:
> 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" <chris.ole...@gmail.com> a écrit dans le message de news:
> 1190033689.772775.180...@19g2000hsx.googlegroups.com...
> 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
>
> > HTH
> > Cordially
> > Pascal
>
> > "Chris" <chris.ole...@gmail.com> a écrit dans le message de news:
> > 1190031530.161597.324...@19g2000hsx.googlegroups.com...
>
> > > 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 With Quote
 
Chris
Guest
Posts: n/a
 
      17th Sep 2007
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

On Sep 17, 9:39 am, Chris <chris.ole...@gmail.com> wrote:
> I've tried that and it still gives the same error
>
> Any other suggestions?
>
> On Sep 17, 9:09 am, "papou" <cpapoupasbon@çanonpluslaposte.net> wrote:
>
> > 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" <chris.ole...@gmail.com> a écrit dans le message de news:
> > 1190033689.772775.180...@19g2000hsx.googlegroups.com...
> > 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
>
> > > HTH
> > > Cordially
> > > Pascal
>
> > > "Chris" <chris.ole...@gmail.com> a écrit dans le message de news:
> > > 1190031530.161597.324...@19g2000hsx.googlegroups.com...
>
> > > > Hello,
>
> > > > I was wondering if it were possible to restrict the values that canbe
> > > > 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 With Quote
 
Chris
Guest
Posts: n/a
 
      17th Sep 2007
Found the answer on another forum...

http://www.tek-tips.com/viewthread.c...=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!

On Sep 17, 10:01 am, Chris <chris.ole...@gmail.com> wrote:
> 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
>
> On Sep 17, 9:39 am, Chris <chris.ole...@gmail.com> wrote:
>
> > I've tried that and it still gives the same error
>
> > Any other suggestions?
>
> > On Sep 17, 9:09 am, "papou" <cpapoupasbon@çanonpluslaposte.net> wrote:
>
> > > 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" <chris.ole...@gmail.com> a écrit dans le message de news:
> > > 1190033689.772775.180...@19g2000hsx.googlegroups.com...
> > > 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
>
> > > > HTH
> > > > Cordially
> > > > Pascal
>
> > > > "Chris" <chris.ole...@gmail.com> a écrit dans le message de news:
> > > > 1190031530.161597.324...@19g2000hsx.googlegroups.com...
>
> > > > > 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 With Quote
 
papou
Guest
Posts: n/a
 
      18th Sep 2007
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" <(E-Mail Removed)> a écrit dans le message de news:
(E-Mail 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

On Sep 17, 9:39 am, Chris <chris.ole...@gmail.com> wrote:
> I've tried that and it still gives the same error
>
> Any other suggestions?
>
> On Sep 17, 9:09 am, "papou" <cpapoupasbon@çanonpluslaposte.net> wrote:
>
> > 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" <chris.ole...@gmail.com> a écrit dans le message de news:
> > 1190033689.772775.180...@19g2000hsx.googlegroups.com...
> > 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
>
> > > HTH
> > > Cordially
> > > Pascal
>
> > > "Chris" <chris.ole...@gmail.com> a écrit dans le message de news:
> > > 1190031530.161597.324...@19g2000hsx.googlegroups.com...
>
> > > > 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 With Quote
 
 
 
Reply

« fill horizontally | Macro: Excel mail attachment - celle-value as Subject of mail »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
OLEObjects - Paste combobox contents to a sheet James Microsoft Excel Programming 4 4th Apr 2009 02:11 PM
Windows forms functionality on the web John Microsoft C# .NET 0 13th Aug 2006 09:05 PM
Help with multiple forms functionality John Cosmas Microsoft Dot NET Framework Forms 3 16th Aug 2005 06:37 PM
Match required - Microsoft Forms 2.0 - Forms.ComboBox.1 Ian Bayly Microsoft Access Form Coding 0 21st Nov 2003 12:35 AM
Referring to OLEObjects (combobox's on worksheet) Ian Chappel Microsoft Excel Programming 4 22nd Sep 2003 05:31 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 01:45 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top