Automated Ticketing

Hi Guys,

Im not actually good in VBA that's why i need a help for those who are master in VBA macro. i have attached the screenshot of what i want to be in my form. i know how to make forms but the coding im not. please help.

on my form there are a combobox that listed all departments ie. ADMIN, Business Intelligence and MIS and when i click the GO ticket! button it will Generate the ticket number consists of MIS (depends on the choice department plus three zeros (000) then 1 (MIS0001)
ticket number is depending on the department choice.

on the textbox: the requested employee should enter only character not numbers (is it possible to have pop msg "wrong input" same with other textbox but in textbox where date need to fillout should contains date mm/dd/yyyy.. is this possible.

on the Remarks section with 3 optionbox whatever i choose with those 3 need to reflect on the summary worksheet.

and then lastly when i click the "put this info on the worksheet" should automatically update the summary spreadsheet.

Please do help me on this.

I dont know how to codes those forms.

many thanks,
albert

on my form there are a combobox that listed all departments ie. ADMIN, Business Intelligence and MIS and when i click the GO ticket! button it will Generate the ticket number consists of MIS (depends on the choice department plus three zeros (000) then 1 (MIS0001)
ticket number is depending on the department choice.


First, I might suggest using a listbox for the dept selection, this way the user is limited to only those options you provide and listboxes work quite a bit the same way... with a combobox they can type in whatever they want.

However, as it is all you need to do is add an event to the COMMAND BUTTON, so that it registers if someone has clicked it... you do this by simply doubleclicking on the button:

Private Sub CommandButton1_Click()
Msgbox "Ticket #: " & ComboBox1 & "000" & "1"
End Sub



on the textbox: the requested employee should enter only character not numbers (is it possible to have pop msg "wrong input" same with other textbox but in textbox where date need to fillout should contains date mm/dd/yyyy.. is this possible.


I dont rememebr what ive done to limit the value input into the textbox in the past but something like this should work:


Private Sub TextBox1_Change()
Dim i As Integer
If Len(TextBox1) > 0 Then 'Only run if length is >= 1 character
For i = 1 To Len(TextBox1) 'Loop through all char in textbox
If IsNumeric(Mid(TextBox1, i, 1)) Then 'Check if char is numeric
TextBox1 = Left(TextBox1, i - 1) & Mid(TextBox1, i + 1) 'Remove the numeric value
End If
Next i
End If
End Sub

As far as the date field goes, change it from a textbox to a Date Picker, you can add the Date Picker by right clicking on your VBA Toolbox (the toolbox is the list of controls that you add to the form) and select Additional Controls>Microsoft Date and Time Picker Control 6.0

Then you can add the date picker control which provides the little popup calendar and the user can type in only a valid date!




and then lastly when i click the "put this info on the worksheet" should automatically update the summary spreadsheet.


This is fairly simple

Sub CommandButton1_Click()
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
.Range("A1") = combobox1
.Range("A2") = TextBox1
End With
End Sub