PDA

View Full Version : Userform Validation Issues



Sir Babydum GBE
08-29-2007, 01:33 PM
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: 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

mdmackillop
08-29-2007, 01:48 PM
Can you post a copy of your WB with dummy data?

Sir Babydum GBE
08-29-2007, 02:06 PM
Can you post a copy of your WB with dummy data?I'm trying...

Bob Phillips
08-29-2007, 02:17 PM
We know ...

mdmackillop
08-29-2007, 02:59 PM
You can set the Frame Visible to False in the Properties window. Use an update event to make it visible
Private Sub txtPhone_AfterUpdate()
FrameAccnt.Visible = True
End Sub


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

mdmackillop
08-29-2007, 03:07 PM
Part 3
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

mdmackillop
08-29-2007, 03:10 PM
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
08-29-2007, 03:18 PM
The workbook.

Sir Babydum GBE
08-30-2007, 01:56 AM
We know ...That's fighting talk where I come from!

(You're lucky I moved)

Sir Babydum GBE
08-30-2007, 02:46 AM
The answers...

MD - thanks so much. Fab.

Sir Babydum GBE
08-30-2007, 07:35 AM
When I set FrameAcct to visible False and apply this Code:

Private Sub txtPhone_AfterUpdate()
FrameAccnt.Visible = True

End Sub

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
08-30-2007, 09:06 AM
Maybe SetFocus as well?

Sir Babydum GBE
08-31-2007, 03:04 AM
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:
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 SubI'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
08-31-2007, 03:39 AM
You could use:
If Len(txtSAPRef) = 13 And txtSAPRef Like "85*" And IsNumeric(txtSAPRef) Then

Bob Phillips
08-31-2007, 03:49 AM
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

Sir Babydum GBE
09-03-2007, 06:56 AM
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: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

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
09-03-2007, 07:30 AM
Have you tried the BeforeUpdate event?

Sir Babydum GBE
09-03-2007, 10:23 AM
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
09-03-2007, 02:53 PM
Try it:

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

Sir Babydum GBE
09-05-2007, 09:18 AM
Try it:


txtSAPRef.SetFocus


It's still getting stuck on this line?

rory
09-05-2007, 09:23 AM
Can you post the workbook? That works fine for me.

Sir Babydum GBE
09-05-2007, 09:34 AM
Can you post the workbook? That works fine for me. I'll try but internet explorer kept failing when i tried last time.

Sir Babydum GBE
09-05-2007, 09:36 AM
Can you post the workbook? That works fine for me.

rory
09-06-2007, 04:19 AM
Are you using the frames for anything other than decorative purposes? If not, I suggest you get rid of them. Frames have something of an erratic history with textboxes (and comboboxes) not getting focus - if you remove the phone and SAPRef textboxes from the frames, then they work just fine.

Sir Babydum GBE
09-06-2007, 04:31 AM
Are you using the frames for anything other than decorative purposes? If not, I suggest you get rid of them. Frames have something of an erratic history with textboxes (and comboboxes) not getting focus - if you remove the phone and SAPRef textboxes from the frames, then they work just fine.I see!

Well thanks for looking into it Rory - got figure out wheter to keep 'em or not now!

Thanks again.

BD

rory
09-06-2007, 04:34 AM
I'm still checking to see if there is any reason why the SetFocus works for some frames but not for others - will let you know if I figure out an easier workaround!