Submit VBA user form to another workbook

[cazzzac]

Hi people,
I'm new to VBA. I have created a userform that successfully submits to another sheet in the same workbook. However, I want to replicate the user form so that 15 or so users can submit their end of day KPIs to a worksheet in a separate, shared workbook - if only macro-enabled workbooks could be shared!

I want the submission button on the user form to do the following:

1. Change the workbook
2. Select the correct sheet in the new workbook
3. Determine the empty row
4. Transfer the information from that form to the empty row

Here is my code:

Private Sub Button_Submit_Click()
'Change Workbook
Dim nwb As Workbook
Set nwb = Workbooks.Open("G:\Time to Complete Tracker - Destination Workbook.xlsx")

'Make Daily_Tracking_Dataset active
nwb.Sheets("daily_tracking_dataset").Activate

'Determine emptyRow
Dim emptyRow As Long
nwb.emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer Information
nwb.Sheets("daily_tracking_dataset").Cells(emptyRow, 1).Value = TextBox1.Value
nwb.Sheets("daily_tracking_dataset").Cells(emptyRow, 2).Value = lstName.Value
nwb.Sheets("daily_tracking_dataset").Cells(emptyRow, 3).Value = txtROIT.Value
nwb.Sheets("daily_tracking_dataset").Cells(emptyRow, 4).Value = txtROISub.Value
nwb.Sheets("daily_tracking_dataset").Cells(emptyRow, 5).Value = txtRefsT.Value
nwb.Sheets("daily_tracking_dataset").Cells(emptyRow, 6).Value = txtRefsC.Value
nwb.Sheets("daily_tracking_dataset").Cells(emptyRow, 7).Value = txtRefsSub.Value
nwb.Sheets("daily_tracking_dataset").Cells(emptyRow, 8).Value = txtReSubT.Value
nwb.Sheets("daily_tracking_dataset").Cells(emptyRow, 9).Value = txtReSubSub.Value
End Sub

The form seems to correctly open the destination workbook.
However, i get a 438 run-time error 'Object doesn't support this property or method' at the line:
'.nwb.emptyRow ='

I also get a whole lot of out of memory errors when i try to run the form again from within the debugger. I think my code is nearly there, but just jumbled enough to not work.
Any suggestions on what i'm doing wrong?

 

[Andrew Poulsom]

Instead of:

nwb.emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

try:

emptyRow = WorksheetFunction.CountA(nwb.Sheets("daily_tracking_dataset").Range("A:A")) + 1

By the way Activating the Worksheet isn't necessary. And your code would be more efficient if you use a With ... End With construct to update the data.

 

[cazzzac]

Thanks you so much! I have spent a long time staring blankly at something which looked like it should work but didn't!
The userform seems to work fine as it is - where would I place the With/End With commands, and what would be the purpose of this?

 

[cazzzac]

Cool - got it working:
With nwb.Sheets("daily_tracking_dataset")
.Cells(emptyRow, 1).Value = TextBox1.Value
.Cells(emptyRow, 2).Value = lstName.Value
.Cells(emptyRow, 3).Value = txtROIT.Value
.Cells(emptyRow, 4).Value = txtROISub.Value
.Cells(emptyRow, 5).Value = txtRefsT.Value
.Cells(emptyRow, 6).Value = txtRefsC.Value
.Cells(emptyRow, 7).Value = txtRefsSub.Value
.Cells(emptyRow, 8).Value = txtReSubT.Value
.Cells(emptyRow, 9).Value = txtReSubSub.Value
.Cells(emptyRow, 10).Value = txtHrsWrkd.Value
End With