• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding UserForm data to existing Rows

A

Aramina

New Member
I have a spreadsheet named 'Overview' where cells A4:A129 are already populated with job types (eg Admin & Clerical Band 4, Maintenance Band 2). The remaining columns are for projected Staff in Post figures over a variety of years/months (so Column B may be for Staff in Post in April 2014, C for April 2015, etc)
I'm trying to create a UserForm that will automatically enter the projected Staff in Post figure for the selected Job type when figures are entered into the boxes on the userform.
I've created the form already with text boxes for staff in post figures to be entered - what I'm having problems with is making it understand that if a Job Type is selected, then the Staff in Post figures must be entered on the same row as that job type - I don't want any new rows at all to be added to the sheet.
I've called my Job type selector 'JobListBox' and its the first entry on the UserForm, and I've used RowSource to tell it populate it with the list of job types from the Overview sheet, under the Userform_Initialize command:

Code: 
With JobListBox
JobListBox.RowSource = "=Overview!A4:A129"
End With

I'm using the 'OK' command button on the Userform as the signal for the data to be entered automaticaly onto the Overview sheet and have the following code attached to it:

Code: 
Private Sub OKButton_Click()
Dim dataRow As Long
With Sheets(1)
dataRow = WorksheetFunction.Match(JobListBox.Value, .Range("A:A"))
.Cells(dataRow, 5).Value = Apr14TextBox.Value
.Cells(dataRow, 8).Value = Jul14TextBox.Value
.Cells(dataRow, 11).Value = Oct14TextBox.Value
.Cells(dataRow, 14).Value = Jan15TextBox.Value
.Cells(dataRow, 15).Value = Apr15TextBox.Value
.Cells(dataRow, 16).Value = Apr16TextBox.Value
.Cells(dataRow, 17).Value = Apr17TextBox.Value
.Cells(dataRow, 18).Value = Apr18TextBox.Value
.Cells(dataRow, 19).Value = ReasonTextBox.Value
End With
End Sub
However, nothing happens when I click the 'OK' button after entering data!
Any help very gratefully received!
Thanks - Aramina
 
can you add a debug line in there to make sure the row is found? something like:
Debug.Print dataRow
after the variable is set? It should print the number to the Immediate window if something is found. I'm also assuming that the first Worksheet in your workbook is the Overview worksheet?
 
Many thanks - I've got it to work now - somehow!
I wondered if you could also help with getting the UserForm to display any existing figures in the textbox, whilst still allowing me to overwrite if I want to?

For example, the spreadsheet might have 3.0 in column B for Admin & Clerical Band 1
Currently my code is:
Apr14TextBox.Value = ""
(where Apr14TexBox corresponds to the cell in column B)
Obviously this shows nothing in the textbox when the UserForm initializes - I can't work out the correct syntax to get it to display any existing data (if any) that the sheet contains in that cell

Cheers
Aramina
 
Should just be able to set the values in the initialize event code.
Example:
Code: 
Private Sub UserForm_Initialize()
TextBox1.Value = Range("C1").Value
End Sub
 
You must log in or register to reply here.
Back
Top