Creating first ever userform but not going how I wanted
 

I am in the process of creating my first userform. I am having much
difficulty though as the information in the Office help is not pertinent to
what I am trying to do.

I am looking to find the flaws in these codes:

Private Sub UserForm_Initialize()
cbxData.Enabled = False
cmdOK = False
End Sub

Private Sub cbxFilter_Change()
cbxData.Enabled = True
cmdOK = False

Dim Department() As Variant
Dim Rank() As Variant
Dim Sex() As Variant

If cbxFilter.Value = "DEPARTMENT" Then
cbxData.RowSource = "Department"

'Department = VBA.Array("Department", _
"Accounting", _
"Finance", _
"Management")

ElseIf cbxFilter.Value = "RANK" Then
cbxData.RowSource = "Rank"

'Rank = VBA.Array("Rank", _
"Assistant", _
"Associate", _
"Full", _
"Instructor")

ElseIf cbxFilter.Value = "SEX" Then
cbxData.RowSource = "Sex"

'Sex = VBA.Array("Sex", _
"F", _
"M")

End If

End Sub

Private Sub cmdCancel_Click()
Unload SelectCriteria
End Sub

Private Sub cmdOK_Click()

If cbxFilter.Value = "Department" Then
Load cbxData.Value = Range("c2")
ElseIf cbxFilter.Value = "Rank" Then
Load cbxData.Value = Range("d2")
ElseIf cbxFilter.Value = "Sex" Then
Load cbxData.Value = Range("f2")

End If

Me.Hide
End Sub


If I can get over this problem then I hopefully can activate my userform can
be run smoothly.

Thank you in advance for your help.

Creating first ever userform but not going how I wanted
 

Might help if you tell us what the purpose of the form is, and what doesn't
work.


--
HTH

Bob Phillips

"WhiteFalcon" wrote in message
...
I am in the process of creating my first userform. I am having much
difficulty though as the information in the Office help is not pertinent
to
what I am trying to do.

I am looking to find the flaws in these codes:

Private Sub UserForm_Initialize()
cbxData.Enabled = False
cmdOK = False
End Sub

Private Sub cbxFilter_Change()
cbxData.Enabled = True
cmdOK = False

Dim Department() As Variant
Dim Rank() As Variant
Dim Sex() As Variant

If cbxFilter.Value = "DEPARTMENT" Then
cbxData.RowSource = "Department"

'Department = VBA.Array("Department", _
"Accounting", _
"Finance", _
"Management")

ElseIf cbxFilter.Value = "RANK" Then
cbxData.RowSource = "Rank"

'Rank = VBA.Array("Rank", _
"Assistant", _
"Associate", _
"Full", _
"Instructor")

ElseIf cbxFilter.Value = "SEX" Then
cbxData.RowSource = "Sex"

'Sex = VBA.Array("Sex", _
"F", _
"M")

End If

End Sub

Private Sub cmdCancel_Click()
Unload SelectCriteria
End Sub

Private Sub cmdOK_Click()

If cbxFilter.Value = "Department" Then
Load cbxData.Value = Range("c2")
ElseIf cbxFilter.Value = "Rank" Then
Load cbxData.Value = Range("d2")
ElseIf cbxFilter.Value = "Sex" Then
Load cbxData.Value = Range("f2")

End If

Me.Hide
End Sub


If I can get over this problem then I hopefully can activate my userform
can
be run smoothly.

Thank you in advance for your help.

Creating first ever userform but not going how I wanted
 

Hi,
I rewrote Initialize and cbxFilter_Change which should make it easier (see
below).
Not sure what you are trying to do with: cmdOk = False
Not sure either for: Load cbxData.Value = Range("c2")
--- not the correct syntax or use for 'Load' . Check online help.

Does the code below do what you are looking for? It be easy to understand
and adjust if necessary.
'------------------------------------------------
Private Sub UserForm_Initialize()
Dim arrFilter As Variant
arrFilter = Array("DEPT", "RANK", "SEX")
cbxFilter.List = arrFilter 'Fill in Filter
cbxFilter.ListIndex = 0 'Set filter to 1st element
End Sub

Private Sub cbxFilter_Change()
Dim arrData As Variant, s As String

s = Strings.UCase(cbxFilter.Value)
Select Case s
Case "DEPT": arrData = Array("Accounting", "Finance", "Management")
Case "RANK": arrData = Array("Assistant", "Associate", "Full",
"Instructor")
Case "SEX": arrData = Array("F", "M")
Case Else: arrData = Array("Missing element in filter")
End Select
cbxData.List = arrData 'Fll up cbxData
cbxData.ListIndex = 0 'Select first element by default
End Sub
'--------------------------------------
--
Regards,
Sébastien

"WhiteFalcon" wrote:

I am in the process of creating my first userform. I am having much
difficulty though as the information in the Office help is not pertinent to
what I am trying to do.

I am looking to find the flaws in these codes:

Private Sub UserForm_Initialize()
cbxData.Enabled = False
cmdOK = False
End Sub

Private Sub cbxFilter_Change()
cbxData.Enabled = True
cmdOK = False

Dim Department() As Variant
Dim Rank() As Variant
Dim Sex() As Variant

If cbxFilter.Value = "DEPARTMENT" Then
cbxData.RowSource = "Department"

'Department = VBA.Array("Department", _
"Accounting", _
"Finance", _
"Management")

ElseIf cbxFilter.Value = "RANK" Then
cbxData.RowSource = "Rank"

'Rank = VBA.Array("Rank", _
"Assistant", _
"Associate", _
"Full", _
"Instructor")

ElseIf cbxFilter.Value = "SEX" Then
cbxData.RowSource = "Sex"

'Sex = VBA.Array("Sex", _
"F", _
"M")

End If

End Sub

Private Sub cmdCancel_Click()
Unload SelectCriteria
End Sub

Private Sub cmdOK_Click()

If cbxFilter.Value = "Department" Then
Load cbxData.Value = Range("c2")
ElseIf cbxFilter.Value = "Rank" Then
Load cbxData.Value = Range("d2")
ElseIf cbxFilter.Value = "Sex" Then
Load cbxData.Value = Range("f2")

End If

Me.Hide
End Sub


If I can get over this problem then I hopefully can activate my userform can
be run smoothly.

Thank you in advance for your help.

Creating first ever userform but not going how I wanted
 

(Cont'd)
Maybe i understand what you are trying to do in the other subs:

- Unloadig the form: is the form called SelectCriteria?
If so: in a userform code, class module, or a sheet module... (but not
for a regular code module) , you can replace the module name by the word Me.
In this example, your form SelectCriteria, if later you rename the form but
within the code you used Me, it will still work. Eg:

'Unloading the form
Private Sub cmdCancel_Click()
Unload Me
End Sub

- Now in the cmdOk_Click: are you trying to send the selected values to C2,
d2,f2? If so, something like (not tested):

Private Sub cmdOK_Click()
dim s as string
s=cbxData.Value

Select Case Strings.UCase(cbxFilter.Value)
case "DEPT": Range("c2")=s
case "RANK": Range("d2") =s
case "SEX": Range("f2") =s
case else: msgbox "missing case"
End Select

Unload Me
End Sub

'--------------------------------------------------------------------

PLease, try to explain more in details and precisely when posting code, it
makes it easier for us and more probable for you to get a reply. thanks.

Regards,
Sébastien

Creating first ever userform but not going how I wanted
 

Thank you for taking the oppourtunity to respond to my request and I
apologize for not going in to more detail.

I am trying to make a userform known as "SelectCriteria" for my company's
employee information database to allow for the activation of an AutoFilter
that will allow the information that a user needs to be accessed when someone
needs it and it must be accessed through a command button within Excel. It is
supposed to be similar to Access but we do not have Access as part of our
network so we are using Excel instead. This is all the information I can
relay. I thank you in advance for your help.

"Bob Phillips" wrote:

Might help if you tell us what the purpose of the form is, and what doesn't
work.


--
HTH

Bob Phillips

"WhiteFalcon" wrote in message
...
I am in the process of creating my first userform. I am having much
difficulty though as the information in the Office help is not pertinent
to
what I am trying to do.

I am looking to find the flaws in these codes:

Private Sub UserForm_Initialize()
cbxData.Enabled = False
cmdOK = False
End Sub

Private Sub cbxFilter_Change()
cbxData.Enabled = True
cmdOK = False

Dim Department() As Variant
Dim Rank() As Variant
Dim Sex() As Variant

If cbxFilter.Value = "DEPARTMENT" Then
cbxData.RowSource = "Department"

'Department = VBA.Array("Department", _
"Accounting", _
"Finance", _
"Management")

ElseIf cbxFilter.Value = "RANK" Then
cbxData.RowSource = "Rank"

'Rank = VBA.Array("Rank", _
"Assistant", _
"Associate", _
"Full", _
"Instructor")

ElseIf cbxFilter.Value = "SEX" Then
cbxData.RowSource = "Sex"

'Sex = VBA.Array("Sex", _
"F", _
"M")

End If

End Sub

Private Sub cmdCancel_Click()
Unload SelectCriteria
End Sub

Private Sub cmdOK_Click()

If cbxFilter.Value = "Department" Then
Load cbxData.Value = Range("c2")
ElseIf cbxFilter.Value = "Rank" Then
Load cbxData.Value = Range("d2")
ElseIf cbxFilter.Value = "Sex" Then
Load cbxData.Value = Range("f2")

End If

Me.Hide
End Sub


If I can get over this problem then I hopefully can activate my userform
can
be run smoothly.

Thank you in advance for your help.