error Transferring Data from form using VBA

bh123

New Member
Joined
Mar 31, 2014
Messages
8
Hi

I have successfully created the code below using an online tutorial. I have two forms whic are transferring to 2 different databases. The first form has worked no problem. I copied the code below to create the second transfer of data and added the number '1' to differentiate the two. It is not working. Please could you help me as where I have gone wrong. i have highlighted in Bold where i added 1. The reset seems to work ok.

thanks

Sub transfer()

Dim objList As ListObject ' to store or refer the table name CovidTestData
Dim shForm As Worksheet ' it will refer to the Worksheet 'Form"

Set objList = ThisWorkbook.Sheets("ResultDatabase").ListObjects("ResultData")
Set shForm = ThisWorkbook.Sheets("Result")

If objList.DataBodyRange(1, 1).Value <> "" Then


objList.ListRows.Add Position:=1



End If

With objList

.DataBodyRange(1, 1).Value = shForm.Range("G8").Value
.DataBodyRange(1, 2).Value = shForm.Range("G10").Value
.DataBodyRange(1, 3).Value = shForm.Range("G12").Value
.DataBodyRange(1, 4).Value = Application.UserName

End With

shForm.Range("G8,G10,G12").Value = ""

ThisWorkbook.Save



End Sub

Sub TransferToTable1()

Dim imessage As VbMsgBoxResult

imessage = MsgBox("Do you want to transfer this information to the Database?", vbYesNo + vbQuestion, "Confirmation")

If imessage = vbNo Then Exit Sub

Call transfer1



End Sub

Sub Reset1()

Dim imessage As VbMsgBoxResult

imessage = MsgBox("Do you want to reset the form?", vbYesNo + vbQuestion, "Confirmation")

If imessage = vbNo Then Exit Sub

Sheets("Result").Range("G8,G10").Value = ""
Sheets("Result").Range("G12").Value = "=now()"

End Sub
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Where is transfer1 subprocedure? Have you defined that.

In the above you have shown only transfer subprocedure.

Thanks,
Saurabh
 

bh123

New Member
Joined
Mar 31, 2014
Messages
8
Hi,

Where is transfer1 subprocedure? Have you defined that.

In the above you have shown only transfer subprocedure.

Thanks,
Saurabh
Thank you for your reply Saurabh. The transfer 1 procedure is in Module1. This is in Module 2. Should I have added the above procedure to Module 1 in my vba?
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,512
Office Version
  1. 2019
Platform
  1. Windows
The transfer 1 procedure is in Module1. This is in Module 2. Should I have added the above procedure to Module 1 in my vba?

Share the actual code you are having issues with & explain in what way it is not working (any errors being reported)?

Dave
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

Thank you for your reply Saurabh. The transfer 1 procedure is in Module1. This is in Module 2. Should I have added the above procedure to Module 1 in my vba?
The location of procedure (module1/ module2) will not impact the result until it's not marked as Private. Also what error are you getting ?

Please share the code of transfer1 also.

Thanks,
Saurabh
 

bh123

New Member
Joined
Mar 31, 2014
Messages
8
Share the actual code you are having issues with & explain in what way it is not working (any errors being reported)?

Dave
I have included the code in the main message above. Is there anything else you may need from me to help me?

The message says:
compile error:
Sub or Function not defined

many thanks for your reply
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,512
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I have included the code in the main message above. Is there anything else you may need from me to help me?

The message says:
compile error:
Sub or Function not defined

many thanks for your reply

The error is clear your Sub transfer1 either does not exist (or you have made it private) yet you stated in #post 3 that it is in module 1?

If you have copied transfer code from one module to another you need to rename it transfer1 and ensure that if it is being called from another module, that it is not private.

If still having issue - place copy of your workbook with dummy data on a file sharing site like dropbox & provide a link to it.

Dave
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,512
Office Version
  1. 2019
Platform
  1. Windows
I am not a google user & not able access your file.

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,129,517
Messages
5,636,804
Members
416,941
Latest member
shazzaxyz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top