VBA Error

germ

New Member
Joined
Jan 15, 2010
Messages
11
I have the following function:

Code:
Private Sub UpdatePrice()
    Sheet1.Range("E4").Value = Sheet2.Range("B1").Value + Sheet1.ComboBox1A.Value
End Sub
I can call it in Excel and it works no problem. When I close the workbook, I get the following error: "member or data member not found" with ComboBox1A highlighted in blue.

Is there any reason why I would get this only when I close the document?

Thanks,
Lee
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
I don't believe so (sorry I am new to Excel VBA).

In my VBA code, I have many list box & combo box change events that call this UpdatePrice function but that is it. In all I only have list box change events, combo box change events, command button click events (which open a picture in a userform) and this private UpdatePrice function.

Is there somewhere else I should look to see why it's seems to be getting fired when I close?

Thanks!
Lee
 
Upvote 0
When the error message appears, do you have a debug button? If so please post the procedure that the error occurs in (and if possible which line).
 
Upvote 0
Here's what happens:
- I click to close the workbook. I asks me if I want to save it. I click either yes or no (both lead to the error).
- Say I click no, then immediately the Microsoft Visual Basic code window opens with an alert box that says "Compile Error: Method or data member not found". So I click OK.
- It then highlights "Private Sub UpdatePrice()" in yellow and "ComboBox1A" in blue. (see code snippet below)
- If I click X to close the window, I same error again "Compile Error: Method or data member not found" - and everything repeats itself...

Hopefully that helps...

Thanks!
 
Upvote 0
Lee

What is this code actually meant to do?

How is it being run? What controls do you have in/on the worksheet?
 
Upvote 0
Norie,

All the function does is add up combo box values and a few numeric field values and puts the total in a cell - it's basically totaling up prices for a configurator.

The function only gets called whenever a combo box is changed - ie.

Code:
Private Sub ComboBox1A_Change()
    Sheet1.Unprotect
    Range("N20").Value = ComboBox1A.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Thanks for the help.
Lee
 
Upvote 0
Why are you calling the code?

Couldn't you just put it in the change event?

That would make more sense and could actually eliminate the problem.:)
 
Upvote 0
I could do that but I've got around 50 combo boxes that I'm adding up - so I thought calling a function would be much easier than duplicating that addition in each change event.

My current UpdatePrice function is much longer that what I supplied but the error still occurs on this simple function I supplied.

Any thoughts on to why this seems to be getting called when I close the worksheet?

Thanks!
 
Upvote 0
Why do you have so many comboboxes?

I think you might want to look at a different approach.

As for the code you posted - it really is helpful if you post the actual code.

It might be lengthy but seeing the 'real' thing might help us help you - there could be something in it that you think is irrelavant but isn't.:)

PS I think the problem could actually be something to do with calling the other sub, but it's hard to be sure about that.

It would really be helpful if you told us where all the code and controls are located.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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
Back
Top