Userform Validation Issues

[Sir Babydum GBE]

Okay, I'm learning, but still finding it difficult to find info on certain userform stuff, so...

One
Say I have Frame1 & Frame2. There are three combo-boxes in each Frame.

I want Frame2 to be hidden (or greyed out) until Combo-box3 of Frame 1 is populated.

How do I do it?

Two
Same as above, but say I only want Frame 2 made visible if the user selects "yes" in Combo-box3 (of frame1)

Three
I have a combo-box named "cboAccType". If the user selects "Single", then another combo-box, "cboOffer", needs to look at named range "listDiscountSingle", whereas if the result of "cboAccType" was "Dual", then "cboOffer" needs to look at named range "listDiscountDual".

This is the code I have so far for initializing the Userform: [vba]Private Sub UserForm_Initialize()
Dim cAccType As Range
Dim cAttitude1 As Range
Dim cAttitude2 As Range
'Dim cDiscount As Range
Dim cElecMtr As Range
Dim cGasMtr As Range
Dim cOutcome As Range
Dim cCustTitle As Range
Dim ws As Worksheet
Set ws = Worksheets("Validations")
For Each cAccType In ws.Range("ListAccountType")
With Me.cboAccType
.AddItem cAccType.Value
.List(.ListCount - 1, 1) = cAccType.Offset(0, 1).Value
End With
Next cAccType
For Each cCustTitle In ws.Range("ListTitle")
With Me.cboTitle
.AddItem cCustTitle.Value
.List(.ListCount - 1, 1) = cCustTitle.Offset(0, 1).Value
End With
Next cCustTitle
For Each cAttitude1 In ws.Range("ListAttitude1")
With Me.cboDescribe1
.AddItem cAttitude1.Value
.List(.ListCount - 1, 1) = cAttitude1.Offset(0, 1).Value
End With
Next cAttitude1
For Each cAttitude2 In ws.Range("ListAttitude2")
With Me.cboDescribe2
.AddItem cAttitude2.Value
.List(.ListCount - 1, 1) = cAttitude2.Offset(0, 1).Value
End With
Next cAttitude2
For Each cElecMtr In ws.Range("ListElecMtr")
With Me.cboElecMtr
.AddItem cElecMtr.Value
.List(.ListCount - 1, 1) = cElecMtr.Offset(0, 1).Value
End With
Next cElecMtr
For Each cGasMtr In ws.Range("ListGasMtr")
With Me.cboGasMtr
.AddItem cGasMtr.Value
.List(.ListCount - 1, 1) = cGasMtr.Offset(0, 1).Value
End With
Next cGasMtr
For Each cOutcome In ws.Range("ListOutcome")
With Me.cboOutcome
.AddItem cOutcome.Value
.List(.ListCount - 1, 1) = cOutcome.Offset(0, 1).Value
End With
Next cOutcome
'For Each cDiscount In ws.Range("ListDiscountDual" 'or "ListDiscountSingle")
'With Me.cboOffer
'.AddItem cDiscount.Value
'.List(.ListCount - 1, 1) = cDiscount.Offset(0, 1).Value
'End With
'Next cDiscount

Me.txtAgent.Value = Environ("UserName")
End Sub[/vba]

[mdmackillop]

Can you post a copy of your WB with dummy data?

[Sir Babydum GBE]

Can you post a copy of your WB with dummy data?

I'm trying...

[Bob Phillips]

We know ...

[mdmackillop]

You can set the Frame Visible to False in the Properties window. Use an update event to make it visible
[VBA]Private Sub txtPhone_AfterUpdate()
FrameAccnt.Visible = True
End Sub
[/VBA]

Similarly, you can look at the Combobox value in the update event and if Yes, then make visible.

[mdmackillop]

Part 3
[VBA]Private Sub cboAccType_Change()
Select Case cboAccType
Case "Single"
cboOffer.List() = Range("listDiscountSingle").Value
Case "Dual"
cboOffer.List() = Range("listDiscountDual").Value
End Select
End Sub

[/VBA]

[mdmackillop]

BTW, I can't see a simple way to Grey Out. You could set Enabled = False and Background Colour to Grey, resetting them in the Update event, if you particularly want that effect.

[mdmackillop]

The workbook.

[Sir Babydum GBE]

We know ...

That's fighting talk where I come from!

(You're lucky I moved)

[Sir Babydum GBE]

The answers...

MD - thanks so much. Fab.

[Sir Babydum GBE]

When I set FrameAcct to visible False and apply this Code:[vba]

Private Sub txtPhone_AfterUpdate()
FrameAccnt.Visible = True

End Sub
[/vba]

The enter or Tab keys have no effect initially. I have to click a different field so that my entry registers as an update. Any way around this?

[Bob Phillips]

Maybe SetFocus as well?

[Sir Babydum GBE]

Set focus works on comboboxes but not on text entries as the focus changes as soon as you type a character.

No worries

With this code:
[vba]Private Sub optAccept_Click()
myleft = Left(txtSAPRef, 2)
Select Case myleft
Case Is = "85"
Select Case optAccept
Case True
LblHouse.Enabled = True
txtHouse.Enabled = True
cboDescribe2.Enabled = False
LblDescribe2.Enabled = False
txtHouse.SetFocus
End Select
Case Is <> "85"
Select Case cboAccType
Case Is = "Elec"
LblElecMtr.Enabled = True
cboElecMtr.Enabled = True
LblGasMtr.Enabled = False
cboGasMtr.Enabled = False
cboDescribe2.Enabled = False
LblDescribe2.Enabled = False
cboElecMtr.SetFocus
Case Is = "OSE"
LblElecMtr.Enabled = True
cboElecMtr.Enabled = True
LblGasMtr.Enabled = False
cboGasMtr.Enabled = False
cboDescribe2.Enabled = False
LblDescribe2.Enabled = False
cboElecMtr.SetFocus
Case Is = "Gas"
LblElecMtr.Enabled = False
cboElecMtr.Enabled = False
LblGasMtr.Enabled = True
cboGasMtr.Enabled = True
cboDescribe2.Enabled = False
LblDescribe2.Enabled = False
cboGasMtr.SetFocus
End Select
End Select
End Sub[/vba]I'm checking that the entry starts with "85", but really I need it to check for entries that begin with 85 AND are 13 character long - and it must be all numbers.

How do I do this please?

Damian

[rory]

You could use:
[VBA]If Len(txtSAPRef) = 13 And txtSAPRef Like "85*" And IsNumeric(txtSAPRef) Then[/VBA]

[Bob Phillips]

[vba]

Private Sub optAccept_Click()

Select Case True

Case Left(txtSAPRef, 2) = "85" And Len(txtSAPRef) = 13
Select Case optAccept
Case True
LblHouse.Enabled = True
txtHouse.Enabled = True
cboDescribe2.Enabled = False
LblDescribe2.Enabled = False
txtHouse.SetFocus
End Select

Case Left(txtSAPRef, 2) <> "85"
Select Case cboAccType
Case Is = "Elec"
LblElecMtr.Enabled = True
cboElecMtr.Enabled = True
LblGasMtr.Enabled = False
cboGasMtr.Enabled = False
cboDescribe2.Enabled = False
LblDescribe2.Enabled = False
cboElecMtr.SetFocus
Case Is = "OSE"
LblElecMtr.Enabled = True
cboElecMtr.Enabled = True
LblGasMtr.Enabled = False
cboGasMtr.Enabled = False
cboDescribe2.Enabled = False
LblDescribe2.Enabled = False
cboElecMtr.SetFocus
Case Is = "Gas"
LblElecMtr.Enabled = False
cboElecMtr.Enabled = False
LblGasMtr.Enabled = True
cboGasMtr.Enabled = True
cboDescribe2.Enabled = False
LblDescribe2.Enabled = False
cboGasMtr.SetFocus
End Select
End Select

End Sub
[/vba]

[Sir Babydum GBE]

Thanks Both,

Used a combination of both your suggestions (for different controls). Marvellous!

I'm still getting some problems though that are odd.

In this code:[vba]Private Sub txtPhone_AfterUpdate()
myPhoneVerify = Left(txtPhone, 1) & Len(txtPhone) & IsNumeric(txtPhone)
Select Case myPhoneVerify
Case Is = "011True"
txtSAPRef.Enabled = True
LblSAPRef.Enabled = True
txtSAPRef.SetFocus
Case Is <> "011True"
MsgBox ("Please entar an 11-digit phone number beginning with 0. Do not use spaces. If you don't know the number use 11 zeros")
End Select
End Sub[/vba]

I get an error "Unexpected call to method or property access". I don't get that if it's a Change Sub (as opposed to AfterUpdate) - but then I can only type one number in before the sub runs - and I need to type 11. So I switch the sub to afterupdate and I get the error (on the setFocus line)

What am I doing wrong?

[rory]

Have you tried the BeforeUpdate event?

[Sir Babydum GBE]

Have you tried the BeforeUpdate event?

If I changed the focus before the update, wouldn't that make it impossible to update the first control?

[rory]

Try it:

[vba]Private Sub txtPhone_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
myPhoneVerify = Left(txtPhone, 1) & Len(txtPhone) & IsNumeric(txtPhone)
Select Case myPhoneVerify
Case Is = "011True"
txtSAPRef.Enabled = True
lblSAPRef.Enabled = True
txtSAPRef.SetFocus
Case Is <> "011True"
MsgBox "Please enter an 11-digit phone number beginning with 0. Do not use spaces. If you don't know the number use 11 zeros"
Cancel = True
End Select
End Sub
[/vba]

[Sir Babydum GBE]

Try it:

[vba]
txtSAPRef.SetFocus
[/vba]

It's still getting stuck on this line?