Drop down menu in User form

[winmaxservices]

Please find the attachment.
In attachment I have created userform called "Details_of_Proposer"

First I need a vba code to call the form for data entry

second I need serial number to be automatially generated starting from one while entering the form detals

third I need drop down list in some fields of the form as given below

In Proposal field
Need options like 1) New Policy, 2) Renewal Policy, 3) Expired policy

In Select any one scheme field
Need options like 1) 2 years policy, 2) 5 years policy 3) 10 years polucy

In M/F field
Need options like 1) Male, 2) Female 3) Transgender

In Gender field
Need options like 1) Male, 2) Female 3) Transgender

In Mode of payment field
Need options like 1) Demand Draft, 2) Cheque 3) Online

Also I want all the data to be saved in Data sheet record wise (all Fields one after one)

[winmaxservices]

here is the attachment

[Leith Ross]

Hello winmaxservices,

Here is the UserForm_Initialize event code that has been added to the attached workbook.

Private Sub UserForm_Initialize()

    Dim n   As Integer
    
      ' In Proposal field
      ' Need options like 1) New Policy, 2) Renewal Policy, 3) Expired policy
        ComboBox9.List = Array("New Policy", "Renewal Policy", "Expired Policy")
    
      ' In Select any one scheme field
      ' Need options like 1) 2 years policy, 2) 5 years policy 3) 10 years polucy
        ComboBox10.List = Array("2 year policy", "5 year policy", "10 year policy")
    
        Me.Gender.List = Array("Male", "Female", "Transgender")
    
        For n = 1 To 8
            Me.Controls("ComboBox" & n).List = Array("Male", "Female", "Transgender")
        Next n
    
      ' In Mode of payment field
      ' Need options like 1) Demand Draft, 2) Cheque 3) Online
        For n = 11 To 14
            Me.Controls("ComboBox" & n).List = Array("Demand draft", "Cheque", "Online")
        Next n
    
End Sub

[winmaxservices]

how to call the form for data entry?

[winmaxservices]

no it is nit running can anyone help on attached file again?

[winmaxservices]

Hi,

Sorry I have done few mistakes in coding, Hence it was not working. Now it is working. Please find attached.

Also I need few more coding in the same form as mentioned below.

1) field = Serial Number: Code need for set Auto serial number starting from number 1
2) field = Date: Code need for enter date only in dd/mm/yyyy
3) field = POD No.: Need code to enter Alphanumeric text
4) field = Recd. Date = Code need for enter date only in dd/mm/yyyy
5) field = Name of the Primary Number = only alphabets Entry
6) field = Contact No. = Only Numeric Number Entry (max 11 characters)
7) field = JIO SA ID No.: Alphanumeric Text
8) field = DOB (there are 8 fields) :=Code need for enter date only in dd/mm/yyyy
9) field = Age (there are 8 fields) :=Code need for enter date only maximum three digit numeric value
10) field = Date (In payment details):=Code need for enter date only in dd/mm/yyyy
11) field = Name (Eight fields) = only alphabets Entry
12) field = Name (In Nominee Details) = only alphabets Entry
13) field = Demand Draft / RTGS / NEFT No (in payment details)=Alphanumberic Text
14) field = Bank Name(in payment details)=Only Alphabets
15) field = Date(In payment details): Code need for enter date only in dd/mm/yyyy
16) field = Amount(In payment details): Only numeric value with 2 decimals.


Need Dependent fields

1) If field "Type of Proposal" is Selected as "New Policy" then field "JIO SA ID" (8 fields) should not be writable.
If field "Type of Proposal" is Selected as "Renewal Policy" then field "JIO SA ID" (8 fields) should be writable. Means JIO SA ID is applicable only for Renewal Policy

2) If field "Scheme" is Selected as "New Policy" then field "JIO SA ID" (8 fields) should not be writable.

Command Buttons

1) "Submit" = To save all details in Respective columns in Sheet "Data Sheet"
2) "Clear Fields"= To clear all the details visible on form
3) "Show Next Record>>" to go to view or edit the details of next record
4) "<<Show Prev Record" to go to view or edit the details of previous record
5) "Enter Recored number to View & Edit" = to enter manually the record number in a cell to view or edit the particular details
6) "Close form" = To close the data entry form

Wants total number of recored enter in data sheet in "Total number of Recorde Entered"

Wants to save all the entered details to be save in Sheet "Data Sheet" after clicking on Submit

Code needed for Sheet "Dashboard"

Total Number of Application Received (Need Total number of Record saved in sheet "Data Sheet"
Date Wise Records: Need code to calculate the number of records entered in Sheet " Data Sheet within specific data (from date and to date)

[Potholes]

Hi,

Sorry I have done few mistakes in coding, Hence it was not working. Now it is working. Please find attached.

Also I need few more coding in the same form as mentioned below.

1) field = Serial Number: Code need for set Auto serial number starting from number 1
2) field = Date: Code need for enter date only in dd/mm/yyyy
3) field = POD No.: Need code to enter Alphanumeric text
4) field = Recd. Date = Code need for enter date only in dd/mm/yyyy
5) field = Name of the Primary Number = only alphabets Entry
6) field = Contact No. = Only Numeric Number Entry (max 11 characters)
7) field = JIO SA ID No.: Alphanumeric Text
8) field = DOB (there are 8 fields) :=Code need for enter date only in dd/mm/yyyy
9) field = Age (there are 8 fields) :=Code need for enter date only maximum three digit numeric value
10) field = Date (In payment details):=Code need for enter date only in dd/mm/yyyy
11) field = Name (Eight fields) = only alphabets Entry
12) field = Name (In Nominee Details) = only alphabets Entry
13) field = Demand Draft / RTGS / NEFT No (in payment details)=Alphanumberic Text
14) field = Bank Name(in payment details)=Only Alphabets
15) field = Date(In payment details): Code need for enter date only in dd/mm/yyyy
16) field = Amount(In payment details): Only numeric value with 2 decimals.


Need Dependent fields

1) If field "Type of Proposal" is Selected as "New Policy" then field "JIO SA ID" (8 fields) should not be writable.
If field "Type of Proposal" is Selected as "Renewal Policy" then field "JIO SA ID" (8 fields) should be writable. Means JIO SA ID is applicable only for Renewal Policy

2) If field "Scheme" is Selected as "New Policy" then field "JIO SA ID" (8 fields) should not be writable.

Command Buttons

1) "Submit" = To save all details in Respective columns in Sheet "Data Sheet"
2) "Clear Fields"= To clear all the details visible on form
3) "Show Next Record>>" to go to view or edit the details of next record
4) "<<Show Prev Record" to go to view or edit the details of previous record
5) "Enter Recored number to View & Edit" = to enter manually the record number in a cell to view or edit the particular details
6) "Close form" = To close the data entry form

Wants total number of recored enter in data sheet in "Total number of Recorde Entered"

Wants to save all the entered details to be save in Sheet "Data Sheet" after clicking on Submit

Code needed for Sheet "Dashboard"

Total Number of Application Received (Need Total number of Record saved in sheet "Data Sheet"
Date Wise Records: Need code to calculate the number of records entered in Sheet " Data Sheet within specific data (from date and to date)

Good afternoon from Brisbane Australia.

I am a 74 year old man and am trying to keep my old brain from drying up, so I have taken up learning all about EXCEL I am running Office 2016,
I would be interested to see the final copy of your document so I can study to see how it all works please.

I can be contacted at briscal@optusnet.com.au

Peter