Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a lookup in a userform | Excel Discussion (Misc queries) | |||
Creating a Userform using code | Excel Programming | |||
Creating Temporary Userform with checkboxes | Excel Programming | |||
Creating a Data Entry Userform | Excel Programming | |||
Need help creating a userform with optionbuttons | Excel Programming |