CODEWITHSUNDEEP
excelvba
CimpleSypher
CimpleSypher

Reputation: 11

Transfer data from UserForm to worksheet

My goal was to document a build process recording inventory tags, then follow up with a checklist that verifies the finished product. Since I'm only in Phase 1 (recording inventory tags) I've got a long way to go.

What I'm trying to do with my UserForm is using a handheld scanner scan the barcodes (inventory tags) and have them populate the underlying spreadsheet when I click on the "Submit" button. However the code below generates this error:

Run Time Error '424' object required

What am I doing wrong?

Private Sub cmdSubmit_Click()
    Dim eRow As Long

      eRow = Database.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

      Cells(eRow, 1).Value = txtDeviceID.Text
      Cells(eRow, 2).Value = txtUserName.Text
      Cells(eRow, 3).Value = txtUserNumber.Text
      Cells(eRow, 4).Value = txtCloneDevice.Text
      Cells(eRow, 5).Value = txtCartAssembly.Text
      Cells(eRow, 6).Value = txtPC.Text
      Cells(eRow, 7).Value = txtMonitor.Text
      Cells(eRow, 8).Value = txtUPS.Text
      Cells(eRow, 9).Value = txtHub.Text
      Cells(eRow, 10).Value = txtKeyboard.Text
      Cells(eRow, 11).Value = txtMouse.Text
      Cells(eRow, 12).Value = txtPrinter.Text
      Cells(eRow, 13).Value = txtWebcam.Text
      Cells(eRow, 14).Value = txtScanner.Text
      Cells(eRow, 15).Value = txtRFID.Text

Upvotes: 0

Views: 9589

Answers (2)

CimpleSypher
CimpleSypher

Reputation: 11

Both Pnuts & Neuralgroove answered the question because I had a few things wrong with my code.

As Pnuts suggested, since this line of code said:

eRow = Database.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Then the subsequent lines should've read:

Database.Cells(eRow, 1).Value = txtDeviceID.Text

Instead of how I originally wrote it:

Cells(eRow, 1).Value = txtDeviceID.Text

I also needed to change the name property of the object to Database as Neuralgroove describes.

Upvotes: 1

neuralgroove
neuralgroove

Reputation: 580

Two solutions (both assume "Database" is a sheet name in your workbook)

  1. You can't reference sheets by name like that. You either have to make its background name "Database" (In the VBA IDE->Project Explorer->Your Project->Microsoft Excel Objects-> Find the sheet named Database, right-click, Properties-> change the "Name" property to "Database") You will then see "Database" in brackets as the underlying sheet name under Microsoft Excel Objects in the Project Explorer and you can reference it in your code.

  2. The other simpler way to do it is to add this to your code.

    Dim Database as Worksheet
Set Database = Worksheets("Database")

Then you can reference it as you are in your code.

CimpleSypher is right, you need to bind the cells call to a sheet, or else it will put the values in whatever sheet is active at the time.

Upvotes: 0

Related Questions