Need assistance with the following code. Debug issue

J15491

New Member
Joined
Jan 10, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have a user form for my users to enter data then, click the save button to save the data to a database. Once they click the save button the form resets to empty text boxes. After I added the below code 2 text boxes autopopulate based on the entry in a combobox. After clicking the Save command button these 2 textboxes do not clear and the debug message box displays. This line of code is highlighted .txtVendorAddress = Application.WorksheetFunction.VLookup((Me.cmbVendor), Sheet5.Range("A2:D30"), 2, 0). I'm learning vba as I go. I'm not sure how to fix this. If someone can give me some direction I would be thankful. This is my final step in completing this project.

Respectfully,

Joseph

Option Explicit

Private Sub cmbVendor_Change()
With Me

.txtVendorAddress = Application.WorksheetFunction.VLookup((Me.cmbVendor), Sheet5.Range("A2:D30"), 2, 0)
.txtVendorLocation = Application.WorksheetFunction.VLookup((Me.cmbVendor), Sheet5.Range("A2:D30"), 3, 0)

End With

End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
446
Office Version
  1. 365
Platform
  1. Windows
Because you are making changes to the controls, associated events are firing up. Application.enableevents won't help here. You can use a module level Boolean variable and use it to prevent these. Wrap your change event codes to check for the Boolean and set it to true/false before and after these kind of changes.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,590
Office Version
  1. 365
Platform
  1. Windows
Another option is to use the Click event rather than the Change event.
 

Forum statistics

Threads
1,175,733
Messages
5,899,157
Members
434,750
Latest member
XLPandit

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