Solved: How to create listing in Combo box of User Form

[winxmun]

I need to create a droplist in a combo box of a user form. I have tried to seek help from Help File but couldnt find. Anyone can help? tks.

[tpoynton]

are the values in a worksheet, or do they not change?

you can use the additem method; look here for what a quick google search turned up.


for more help, post a sample workbook!

[winxmun]

Hi, further request/ help is needed.
1. How to fix the date format in the User Form? eg. User must input a prefix date format at the text box, ie dd-mm-yyyy.
2. How to make the Account No. as mandatory field to pick before entering other data?
3. How to create a droplist for user to pick in the User Form. eg. a list of "Agnes, Billy, Sally" in the Maker Name combo box.

Tks a lot!

[winxmun]

Hi, anyone can help?

[herzberg]

Hi, further request/ help is needed.
1. How to fix the date format in the User Form? eg. User must input a prefix date format at the text box, ie dd-mm-yyyy.

I'm not sure how to get this done. I've done something similar before and what I did was to have 3 combo boxes - one each for day, month and year. Users would then select the values and I'll have some code to concatenate the values into the format I define and write them back to the worksheet.

2. How to make the Account No. as mandatory field to pick before entering other data?

There's a few ways to go about that and personally, I think it's the best to educate the users on the proper way of using the form. Programmatically, you can do this:
[vba] Sub CheckAcc()

Dim ActiveCtl As Control

If cmbAcNo.Value = "" Then
MsgBox "Please select an account number!"
For Each ActiveCtl In Me.Controls
If TypeName(ActiveCtl) = "TextBox" Then
ActiveCtl.Text = ""
End If
Next ActiveCtl
Exit Sub
End If
End Sub[/vba] It's not exactly what you are asking for but I think it's close enough. It basically clears all the text boxes if the account number combo box is empty. Have the "Change" button click event call this procedure and they'll have second thoughts about not selecting an account number first in future.

3. How to create a droplist for user to pick in the User Form. eg. a list of "Agnes, Billy, Sally" in the Maker Name combo box.

Here's one way:
[vba]
Private Sub UserForm_Initialize()
cmbMakerName.AddItem "Agnes"
'Repeat the above as needed, changing the name in quotes
End Sub[/vba]

[winxmun]

Hi herzberg, I have tried your method for item 2 & 3 & it is successful. As for item 1, i am not sure how to combine 3 combo box into 1. Furthermore, i think it is better to remain as 1 test box. Tks a lot.

Few more questions:
1. How to prefix text box to input Number only (Number in Currency, percentage format).
2. How to allow multiple log in for updating in the User Form? I tried to save as Share Workbook but encounter error & Macro is disabled.
3. How to prefix text box to date format in dd-mm-yyyy?

tks a lot.

[tpoynton]

is this homework?

[Northwolves]

3. How to create a droplist for user to pick in the User Form. eg. a list of "Agnes, Billy, Sally" in the Maker Name combo box.

Tks a lot!

[VBA]Private Sub UserForm_Initialize()
Call SortList
With Sheets("AutoReview")
cmbAcNo.List = Range(.Cells(3, "IV"), .Cells(3, "IV").End(xlDown)).Value
Range(.Cells(1, "IV"), .Cells(1, "IV").End(xlDown)).ClearContents
End With
cmbMakerName.List = Split("Agnes Billy Sally")
End Sub[/VBA]

Best Regards
Northwolves

[herzberg]

When I mention about having 3 combo boxes for the dates, it looks like the picture I've attached. When the user clicks "OK", some code goes:
[vba]
Sub MergeDate()
Dim myDate As String
myDate = txtDate.Text & "/" & txtMonth.Text & "/" & txtYear.Text
Sheets(1).Range("A1").Value = myDate
End Sub[/vba] The above gives me the flexibility to arrange the date format by simply rearranging the order of the 3 values.

Few more questions:
1. How to prefix text box to input Number only (Number in Currency, percentage format).

I'm not sure if that can be done but if you need to check for numbers, here's one way:
[vba]
If IsNumeric(txtTest.Text) = False Then
MsgBox "What's the matter with you? Didn't you read? NUMBERS ONLY!!"
txtTest.Text = ""
Exit Sub
End If[/vba] You may want to change the message to suit your needs; I usually train my users well, so if they do something daft, I think they deserve abuses from Excel.

2. How to allow multiple log in for updating in the User Form? I tried to save as Share Workbook but encounter error & Macro is disabled.

I'm afraid I can't help you on this one.

3. How to prefix text box to date format in dd-mm-yyyy?

I don't think a text box can be set to a particular format. As workarounds, there are a few ways - set up individual combo boxes/text boxes for day, month and year values; using a calendar control for date selection instead of manual entry; educating users that they have to enter the dates in the way you want them to or nothing works and it's their fault, etc...

[mikerickson]

[VBA]Private Sub TextBox2_AfterUpdate()
With Me.TextBox2
If IsDate(.Text) Then
.Text = Format(DateValue(.Text), "dd-mm-yyyy")
.Tag = .Text
Else
.Text = .Tag
End If
End With
End Sub

Private Sub UserForm_Activate()
With Me.TextBox2
.Text = Format(Date, "dd-mm-yyyy")
.Tag = .Text
End With
End Sub[/VBA]

[winxmun]

Dear Herzberg, Northwolves, mikerickson, tpoynton,
Tks a lot for the help. I have "so call" solved all my problems except for multiple log in for file with Macro. Anyway i will mark this thread as solved & re-post a new one for multiple log in issue.

I have also attached the file for everyone's reference, just in case other people have the same issues as me....

Tks all once again!!!