Posting to next available of variable worksheet

[crafter85]

Hello

I've got code which creates a new worksheet with the name of your choice (an employee's name) with a view to creating a staff database with individual records.

The next stage of the process is to input all their details. At the moment, these details are inputted on to the next available row on the "Master" spreadsheet. I've decided what I actually need to do is input them on to Row 42 or the next available row (same difference) on the new record which will change each time so doesn't necessarily have to be next available row if the row number can be fixed.

At this stage, the user is currently on the new record worksheet so rather than instruct to move to a specific named worksheet - as this will change each time for each new employee - I'd like to input the data into row 42/ next available row into the current or active worksheet but can't get it to work. This is what I have so far but need the final destination of the data to be the active worksheet (which is also the same name as textbox_recordname in case that helps):

Private Sub Cmdbutton_add_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Master")


'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'check for a Name number
If Trim(Me.textbox_name.Value) = "" Then
Me.textbox_name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.textbox_name.Value
ws.Cells(iRow, 2).Value = Me.textbox_surname.Value
ws.Cells(iRow, 3).Value = Me.textbox_dob.Value
ws.Cells(iRow, 4).Value = Me.textbox_address1.Value
ws.Cells(iRow, 5).Value = Me.textbox_address2.Value
ws.Cells(iRow, 6).Value = Me.textbox_address3.Value
ws.Cells(iRow, 7).Value = Me.textbox_address4.Value
ws.Cells(iRow, 8).Value = Me.textbox_postcode.Value
ws.Cells(iRow, 9).Value = Me.textbox_jobtitle.Value
ws.Cells(iRow, 10).Value = Me.textbox_startdate.Value
ws.Cells(iRow, 11).Value = Me.textbox_employmenttype.Value
ws.Cells(iRow, 12).Value = Me.textbox_enddate.Value
ws.Cells(iRow, 13).Value = Me.textbox_salary.Value
ws.Cells(iRow, 14).Value = Me.textbox_payscale.Value
ws.Cells(iRow, 15).Value = Me.textbox_hours.Value
ws.Cells(iRow, 16).Value = Me.textbox_holiday.Value
ws.Cells(iRow, 17).Value = Me.textbox_recordname.Value


MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.textbox_name.Value = ""
Me.textbox_surname.Value = ""
Me.textbox_dob.Value = ""
Me.textbox_address1.Value = ""
Me.textbox_address2.Value = ""
Me.textbox_address3.Value = ""
Me.textbox_address4.Value = ""
Me.textbox_postcode.Value = ""
Me.textbox_jobtitle.Value = ""
Me.textbox_startdate.Value = ""
Me.textbox_employmenttype.Value = ""
Me.textbox_enddate.Value = ""
Me.textbox_salary.Value = ""
Me.textbox_payscale.Value = ""
Me.textbox_hours.Value = ""
Me.textbox_holiday.Value = ""
Me.textbox_recordname.Value = ""
Me.textbox_name.SetFocus
End Sub

 

[dmt32]

Hi,
see if these changes to your code does what you want:

Private Sub Cmdbutton_add_Click()
Dim iRow As Long
Dim ws As Worksheet


On Error GoTo myerror
Set ws = Worksheets(textbox_recordname.Text)




'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
If iRow < 42 Then iRow = 42


'rest of your code


myerror:
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave

 

[crafter85]

My hero! Has worked perfectly. I'm hoping this now saves me from doing a load more VB coding - I've only got one more bit to go and I haven't decided how I'm going to tackle it...

 

[crafter85]

OK, so I've failed in the next step! I need the data to also appear on the next available line on the Master worksheet - ideally linked to Row 42 on the record worksheet so when I update the record (any record for any member of staff), the master is updated as well. Is there code I could use for this in the same way you can use =A1 for example?

Otherwise, how do I simply get the row of data to appear on the next available row of the Master spreadsheet as well as on Row 42 of the current worksheet?

Many thanks

 

[dmt32]

=crafter85;4199956

Otherwise, how do I simply get the row of data to appear on the next available row of the Master spreadsheet as well as on Row 42 of the current worksheet?

Hi,
not able to test but see if following does what you want:

Place following in your forms code page:

Private Sub Cmdbutton_add_Click()
    Dim iRow As Long, mRow As Long
    Dim ws As Worksheet, wsMaster As Worksheet
    Dim i As Integer
    
    If Len(Trim(Me.textbox_name.Value)) > 0 Then
    
    On Error GoTo myerror
    Set ws = Worksheets(textbox_recordname.Text)
    Set wsMaster = Worksheets("Master")
    
    
    
    'find next empty row in selected sheet
    iRow = GetRow(sh:=ws)
    If iRow < 42 Then iRow = 42
    
    'find next empty row in master database
    mRow = GetRow(sh:=wsMaster)
    
    For i = LBound(ControlsArray) To UBound(ControlsArray)
    
    With Me.Controls(ControlsArray(i))
        ws.Cells(iRow, i).Value = .Text
        wsMaster.Cells(mRow, i).Value = .Text
        .Text = ""
    End With
    
    Next i
    
    
    MsgBox "Data added", 48, "Data Added"
    
    Else


    MsgBox "Please complete the form", 16, "Entry Required"


    End If
    
myerror:
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
    Me.textbox_name.SetFocus
End Sub

Place following code in standard module:

Option Base 1


Function GetRow(ByVal sh As Object) As Long
    GetRow = sh.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
End Function



Function ControlsArray()
    ControlsArray = Array("textbox_name", "textbox_surname", "textbox_dob", "textbox_address1", _
    "textbox_address2", "textbox_address3", "textbox_address4", "textbox_postcode", _
    "textbox_jobtitle", "textbox_startdate", "textbox_employmenttype", "textbox_enddate", _
    "textbox_salary", "textbox_payscale", "textbox_hours", "textbox_holiday", "textbox_recordname")
End Function

Note the Option Base 1 statement which MUST be placed at the very TOP of the module.

You will need to ensure that the control names in ControlsArray Function are both correct & in the correct order - adjust as required.


Hope helpful

Dave.

 

[crafter85]

Hi Dave

This is a little bit beyond my capability! The code trips over on "GetRow" at


iRow = GetRow(sh:=ws)
If iRow < 42 Then iRow = 42

And I'm afraid I'm tripping up at the Standard Module bit!


As much as I'd love to be able to write VB, it might be a little out of my reach for now! Thank you so much for your help thus far - it pretty much does what I need it to - I'll just have to fudge the last part!

 

[dmt32]

It will "Trip Up" if you have not copied the Functions - You need to insert a standard module
From VB menu Insert >Module

and place All code mentioned there.

Hopefully, should then do what you want.

Dave

 

[crafter85]

I thought I had (inserted a Module and then pasted the code as in your description)

I'll leave it for when it's a bit cooler and I've more time to figure it out without my head melting!

 

[dmt32]

I thought I had (inserted a Module and then pasted the code as in your description)

I'll leave it for when it's a bit cooler and I've more time to figure it out without my head melting!

Your choice but out of interest - is the error you are getting "Sub or Function Not Defined"?

Dave

 

[crafter85]

I was being an idiot (or it cooled down...), it works!!

Thank you so much