VBA Userform Autofill

[AndrewGKenneth]

Hi there,

Would anyone be able to help? I am trying to create a code for my Userform to autofill certain textboxes based on the value of the initial textbox. The initial textbox that the code would be based on is Textbox_JobNumber.

To explain in more detail there will be multiple duplicate entries in the Textbox_JobNumber section of the userform, so that one job number has multiple duplicate entries. But there will also be multiple duplicate values in a range of other textboxes with the names Textbox_LotNumber, Textbox_ProductNumber, TextBox_PartName, Textbox_DrawingNumber, TextBox_Customer and Textbox_Order. Each textbox has a different value from each other (i.e. Text_productnumber is different to TextBox_DrawingNumber) but will always be the same within themselves as long as the job number is the same. (i.e. TextBox_Product Number will always be say 1234 for that specific job number)

For example, TextBox_JobNumber has value J1815211
TextBox_PartName is HF.11X33J

These values will remain the same for each textbox until the job number is changed. Therefore, I need code to recognise in the userform that all entries for Job Number J1815211, will be the same in each textbox until the job number changes. I.E. textbox_order, textbox_customer will be the same value until a new job number is entered. So this would mean the userform only has to be filled in manually for the first entry of each job number and then it will recognise and duplicate the data with autofill.

Please let me know if this is possible or if you need anymore information, as I am new to VBA.

Thanks so much,
Andrew

 

[Johnny C]

it is possible. Open the userform, then double click the textbox you want to change. That opens the sheet of code for the page. At top left you should see the name of the textbox you just clicked. At top right is a list of 'events' that you can select, the code will run when one of those events is triggered.

You might want to consider using comboboxes instead of text boxes, to allow the user to select the valid items relating to that job number

Select the change event, and you should get this:

Private Sub TextBox_JobNumber_Click()

End Sub

In here, put the code to rebuild the other textboxes.

The sort of code you will need for populating comboboxes is :-

   With Userform1.ComboBox_LotNumber
        .Clear
        For intCount = 1 To {whatever}
            strLotNum = {whatever}
            .AddItem strLotNum
        Next intCount
    End With

where Userform1 is the form

If all the boxes change you'll need to code each separately within the one change event. You'll need to repeat for each textbox or combobox.

HTH

 

[AndrewGKenneth]

Thanks Johnny that is really useful. Two quick questions how would I do code to rebuild the other textboxes? Could you give a quick example? And also with the comboboxes there could be hundreds of Job Numbers, wouldn't the combobox dropdown list get too long and become unmanageable?

Thanks again,
Andrew

 

[Johnny C]

Ok I didn't read your question that closely.

To confirm, once a given job number is typed in, the values to go into
Textbox_LotNumber,
Textbox_ProductNumber,
TextBox_PartName,
Textbox_DrawingNumber,
TextBox_Customer and
Textbox_Order

are all fixed?

Assuming you have some sort of table with those values in, once the job number is typed in you need to pull the values corresponding to that job number.
How you do that is up to you, you could use a section of a worksheet or use VBA to get them. I'd use VBA using (if for example your lookup sheet is called JNLookup)

lngJNRow = Sheets("JNLookup").Range("$A:$A").Find(Userform1.TextBox_JobNumber.Value).Row

to get the row with the Job Number in the table (if the JobNumber is Col A) and

lngProductNumber = Range("B"&lngJNRow).Value

to get corresponding Product number in the table (if the ProductNumber is Col B) and
and so on. if the data is in different lookup tables just find the JN row and get the value with that.

Then in your TextBox item on the userform (double click textbox) you need something like this:

Private Sub TextBox_JobNumber_Change()
Dim lngLotNumber As Long, lngProductNumber As Long, lngDrawingNumber As Long
Dim strPartName As String, strCustomer As String
Dim curOrderValue As Currency

{get the data relating to the new jobnumber into the variables above}

userform1.Textbox_LotNumber.Value = lngLotNumber
userform1.Textbox_ProductNumber.Value = lngProductNumber
userform1.TextBox_PartName.Value = strPartName
userform1.Textbox_DrawingNumber.Value = lngDrawingNumber
userform1.TextBox_Customer.Value = strCustomer 
userform1.Textbox_Order.Value = curOrderValue

The Dim specify the data types I think you'd use, change as needed.

You'd need to add error traps in case the job number they entered was invalid.

PS I assumed Userform1 is your form. if you're getting into using forms it's always a good idea to stick the form name before any object. Sometimes VBA gets upset if you don't, and if you're using multiple Userforms you need to avoid ambiguity. So it's just good practice and a good habit to learn from the start.

 

[AndrewGKenneth]

Thanks Johnny, I couldn't have asked for a clearer description. I think even I can follow this! I will give it a go tomorrow when I am back in the office.

 

[AndrewGKenneth]

Hi Johnny,

A quick question where does the first bit of code go ?

Thanks,
Andrew

 

[AndrewGKenneth]

This part : lngJNRow = Sheets("JNLookup").Range("$A:$A").Find(Userform1.TextBox_JobNumber.Value).Row

Thanks so much! Almost there I think!

 

[Johnny C]

Hi Andrew
I'd put that as the first statement after the Dim's in the textbox change sub.

Private Sub TextBox_JobNumber_Change()
Dim rngJNRow as Range, lngJNRow&
Dim lngLotNumber&, lngProductNumber&, lngDrawingNumber&, 
Dim strPartName$, strCustomer$
Dim curOrderValue@

'  Find which row the job number details are in
Set rngJNRow = Sheets("JNLookup").Range("$A:$A").Find(Userform1.TextBox_JobNumber.Value)

if rngJNRow is Nothing then
    Msgbox "No such Job number in the JNLookup sheet"
    Exit Sub
End if
lngJNRow = rngJNRow.Row

'  Now get the data relating to the new jobnumber 
lngLotNumber = Sheets("JNLookup").Range("$B"&lngJNRow).Value
' etc etc.

Obviously replace the sheetname and ranges to your sheets.

I don't know how familiar you are with VBA, so apologies if I'm explaining stuff you already know.

1) You need to ensure that the job number typed exists (a list or combobox wouldn't have that possibility). To do that you need to create a range (rngJNRow above) using a Set and test if the range has any members then set the row number variable to the .Row property of the range.

2) I've used the abbreviated data type definitions above. & = long integer, $ = string, @ = currency, range doesn't have a character abbreviation as far as I know.

 

[AndrewGKenneth]

Hi Johnny,

I have used the following code and made it relevant to my workbook, however it doesn't seem to be working and I keep getting the error message "Run-time error '1004' Application defined or object defined error'. Do I need to code anywhere else apart from the textbox change?

Private Sub TextBox_JobNumber_Change()
Dim rngJNrow As Range, lngJNrow&
Dim lngLotNumber As Long, lngProductNumber As Long, lngDrawingNumber As Long
Dim lngPartname As Long, lngCustomer As Long, lngOrder As Long


Set rngJNrow = Sheets("Sheet1").Range("$A:$A").Find(UserForm1.TextBox_JobNumber.Value)


lngLotNumber = Sheets("Sheet1").Range("$F" & lngJNrow).Value
lngProductNumber = Sheets("Sheet1").Range("$G" & lngJNrow).Value
lngPartname = Sheets("Sheet1").Range("$H" & lngJNrow).Value
lngDrawingNumber = Sheets("Sheet1").Range("$I" & lngJNrow).Value
lngCustomer = Sheets("Sheet1").Range("$J" & lngJNrow).Value
lngOrder = Sheets("Sheet1").Range("$K" & lngJNrow).Value


End Sub

Thanks again!

 

[AndrewGKenneth]

The difficult thing is that we don’t know the other values etc lotnumber, productnumber etc until the job number has been created by the user. There is not set list of job numbers and corresponding values. So it will need to work based on the fact that the user will only need to input lotnumber, product number etc on the first input. Then the job number will link to the other values. If that makes sense ?