Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking for errors in combobox's
I'll try to keep this short. I have a form with a number of combobox's
(let's say 20). Each combobox represents a slot in a shelf. Each combobox points to the same list of cards. Certain slots can accept any card, whereas certain slots can only accept a few cards. I do not want to allow the run button to be selected until all slots contain the correct cards. I can accomplish this with the following code using many if statements for each combobox. Let's say CBox1 (slot-1), can contain any card except "card1 or card2) Private Sub CmdRun_Click() If (frmMaintest.CBox1.Text = "card1") Or (frmMaintest.CBox1.Text = "card2") Then MsgBox "You must select another name!" Exit Sub ElseIf (frmMaintest.CBox1.Text < "card1") Eqv (frmMaintest.CBox1.Text < "card2") Then MsgBox "This name is acceptable!" End If Range("d4").Select Selection.Value = frmMaintest.CBox1.Text Unload frmMaintest End Sub I have also accomplished this using Private Sub CBox1_Change() 'instead of the CmdRun_Click() QUESTIONS 1) Can the above "If" statements be shortened? 2) What I really want is for the combobox to "drop-down" and make you select the correct card before letting you select the next combobox. Any suggestions will be greatly appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
checking for errors in combobox's
Bob
Here's one way to do it. For each combobox on your form, put the cards that are allowed in the Tag property (design time is probably best). Then, when the form loads (or whatever time you determine where the list of cards is), only add cards to the combobox that are in the tag. That way, the user can't make a mistake. Here's an example Sheet1.Range("a1:a3") contains Card1 Card2 Card3 Userform1 has three comboboxes with Tag properties of Card1;Card2 Card2;Card3 All respectively. In the initialize event of the form, I populate the comboboxes based on their Tags Private Sub UserForm_Initialize() Dim CardRng As Range Dim Cell As Range Dim Ctl As Control Dim AllowedCards As Variant Dim i As Long Set CardRng = Sheet1.Range("a1:A3") For Each Ctl In Me.Controls If TypeName(Ctl) = "ComboBox" Then If Len(Ctl.Tag) 0 Then If Ctl.Tag = "All" Then For Each Cell In CardRng.Cells Ctl.AddItem Cell.Value Next Cell Else AllowedCards = Split(Ctl.Tag, ";") For Each Cell In CardRng.Cells For i = LBound(AllowedCards) To _ UBound(AllowedCards) If Cell.Value = AllowedCards(i) Then Ctl.AddItem Cell.Value End If Next i Next Cell End If End If End If Next Ctl End Sub -- Dick Kusleika MVP - Excel www.dicks-clicks.com Post all replies to the newsgroup. "Bob C" wrote in message ... I'll try to keep this short. I have a form with a number of combobox's (let's say 20). Each combobox represents a slot in a shelf. Each combobox points to the same list of cards. Certain slots can accept any card, whereas certain slots can only accept a few cards. I do not want to allow the run button to be selected until all slots contain the correct cards. I can accomplish this with the following code using many if statements for each combobox. Let's say CBox1 (slot-1), can contain any card except "card1 or card2) Private Sub CmdRun_Click() If (frmMaintest.CBox1.Text = "card1") Or (frmMaintest.CBox1.Text = "card2") Then MsgBox "You must select another name!" Exit Sub ElseIf (frmMaintest.CBox1.Text < "card1") Eqv (frmMaintest.CBox1.Text < "card2") Then MsgBox "This name is acceptable!" End If Range("d4").Select Selection.Value = frmMaintest.CBox1.Text Unload frmMaintest End Sub I have also accomplished this using Private Sub CBox1_Change() 'instead of the CmdRun_Click() QUESTIONS 1) Can the above "If" statements be shortened? 2) What I really want is for the combobox to "drop-down" and make you select the correct card before letting you select the next combobox. Any suggestions will be greatly appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I remove multiple 'errors' in Excel error checking, at the same time??? | Excel Discussion (Misc queries) | |||
Spell Checking with checking cell notes | Excel Discussion (Misc queries) | |||
Excel Throwing Circular Errors When No Errors Exist | Excel Worksheet Functions | |||
Checking excel for errors/inconsistencies | Excel Worksheet Functions | |||
Unresolved Errors in IF Statements - Errors do not show in results | Excel Worksheet Functions |