VBA Error

G

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
 
Sort by date Sort by votes
Norie,

This project is building a configurator - so each different combo box is a different option. Everything is located in Sheet1 with the combo boxes pulling in their data from Sheet2. The weird part of this is when I change a value in a combo box, the UpdatePrice function works perfectly - no error. Its just when I close the workbook. At any rate, I will post my entire VBA code below.

Thanks again,
Lee

Code: 
Private Sub ComboBox1_Change()
    Sheet1.Unprotect
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox10b_Change()
    Sheet1.Unprotect
    Range("N64").Value = ComboBox10b.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox10d_Change()
    Sheet1.Unprotect
    Range("N68").Value = ComboBox10d.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox11a_Change()
    Sheet1.Unprotect
    Range("N71").Value = ComboBox11a.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox11b_Change()
    Sheet1.Unprotect
    Range("N72").Value = ComboBox11b.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox11c_Change()
    Sheet1.Unprotect
    Range("N73").Value = ComboBox11c.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox11d_Change()
    Sheet1.Unprotect
    Range("N74").Value = ComboBox11d.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox11e_Change()
    Sheet1.Unprotect
    Range("N75").Value = ComboBox11e.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox11f_Change()
    Sheet1.Unprotect
    Range("N76").Value = ComboBox11f.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox11g_Change()
    Sheet1.Unprotect
    Range("N77").Value = ComboBox11g.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox11h_Change()
    Sheet1.Unprotect
    Range("N78").Value = ComboBox11h.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox11i_Change()
    Sheet1.Unprotect
    Range("N79").Value = ComboBox11i.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox11j_Change()
    Sheet1.Unprotect
    Range("N80").Value = ComboBox11j.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12a_Change()
    Sheet1.Unprotect
    Range("N84").Value = ComboBox12a.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12b_Change()
    Sheet1.Unprotect
    Range("N85").Value = ComboBox12b.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12c_Change()
    Sheet1.Unprotect
    Range("N86").Value = ComboBox12c.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12d_Change()
    Sheet1.Unprotect
    Range("N87").Value = ComboBox12d.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12e_Change()
    Sheet1.Unprotect
    Range("N88").Value = ComboBox12e.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12f_Change()
    Sheet1.Unprotect
    Range("N89").Value = ComboBox12f.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12g_Change()
    Sheet1.Unprotect
    Range("N90").Value = ComboBox12g.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12h_Change()
    Sheet1.Unprotect
    Range("N91").Value = ComboBox12h.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12i_Change()
    Sheet1.Unprotect
    Range("N92").Value = ComboBox12i.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12j_Change()
    Sheet1.Unprotect
    Range("N93").Value = ComboBox12j.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12k_Change()
    Sheet1.Unprotect
    Range("N94").Value = ComboBox12k.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12l_Change()
    Sheet1.Unprotect
    Range("N96").Value = ComboBox12l.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12m_Change()
    Sheet1.Unprotect
    Range("N97").Value = ComboBox12m.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12n_Change()
    Sheet1.Unprotect
    Range("N98").Value = ComboBox12n.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12o_Change()
    Sheet1.Unprotect
    Range("N99").Value = ComboBox12o.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12p_Change()
    Sheet1.Unprotect
    Range("N100").Value = ComboBox12p.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12q_Change()
    Sheet1.Unprotect
    Range("N101").Value = ComboBox12q.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox12r_Change()
    Sheet1.Unprotect
    Range("N102").Value = ComboBox12r.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox13a_Change()
    Sheet1.Unprotect
    Range("N105").Value = ComboBox13a.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox13b_Change()
    Sheet1.Unprotect
    Range("N106").Value = ComboBox13b.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox13c_Change()
    Sheet1.Unprotect
    Range("N107").Value = ComboBox13c.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox13d_Change()
    Sheet1.Unprotect
    Range("N108").Value = ComboBox13d.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox13e_Change()
    Sheet1.Unprotect
    Range("N109").Value = ComboBox13e.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox14a_Change()
    Sheet1.Unprotect
    Range("N114").Value = ComboBox14a.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox15a_Change()
    Sheet1.Unprotect
    Range("N117").Value = ComboBox15a.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

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

Private Sub ComboBox1B_Change()
    Sheet1.Unprotect
    Range("N21").Value = ComboBox1B.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox1C_Change()
    Sheet1.Unprotect
    Range("N22").Value = ComboBox1C.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox2_Change()
    Sheet1.Unprotect
    Range("N25").Value = ComboBox2.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub


Private Sub ComboBox3a_Change()
    Sheet1.Unprotect
    Range("N28").Value = ComboBox3a.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox3b_Change()
    Sheet1.Unprotect
    Range("N30").Value = ComboBox3b.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox3c_Change()
    Sheet1.Unprotect
    Range("N31").Value = ComboBox3c.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox3d_Change()
    Sheet1.Unprotect
    Range("N32").Value = ComboBox3d.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox3e_Change()
    Sheet1.Unprotect
    Range("N33").Value = ComboBox3e.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox3f_Change()
    Sheet1.Unprotect
    Range("N34").Value = ComboBox3f.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox4a_Change()
    Sheet1.Unprotect
    Range("N37").Value = ComboBox4a.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox4b_Change()
    Sheet1.Unprotect
    Range("N38").Value = ComboBox4b.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox4c_Change()
    Sheet1.Unprotect
    Range("N40").Value = ComboBox4c.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox5a_Change()
    Sheet1.Unprotect
    Range("N42").Value = ComboBox5a.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox5b_Change()
    Sheet1.Unprotect
    Range("N43").Value = ComboBox5b.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox6_Change()
    Sheet1.Unprotect
    Range("N46").Value = ComboBox6.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox7a_Change()
    Sheet1.Unprotect
    Range("N49").Value = ComboBox7a.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox8_Change()
    Sheet1.Unprotect
    Range("N52").Value = ComboBox8.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox9a_Change()
    Sheet1.Unprotect
    Range("N55").Value = ComboBox9a.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox9b_Change()
    Sheet1.Unprotect
    Range("N56").Value = ComboBox9b.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox9c_Change()
    Sheet1.Unprotect
    Range("N57").Value = ComboBox9c.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ComboBox9d_Change()
    Sheet1.Unprotect
    Range("N58").Value = ComboBox9d.Value
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub CommandButton1_Click()
    UserForm2.Show
End Sub

Private Sub CommandButton3_Click()
    UserForm4.Show
End Sub

Private Sub CommandButton4_Click()
    UserForm1.Show
End Sub

Private Sub ListBox10a_Change()
    Sheet1.Unprotect
    Dim i As Long
    Dim tempPrice As Double
    tempPrice = 0
    
    For i = LBound(ListBox10a.List) To UBound(ListBox10a.List)
        If ListBox10a.Selected(i) = True Then
            tempPrice = tempPrice + ListBox10a.List(i)
        End If
    Next i
    
    Range("N61").Value = tempPrice
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ListBox10c_Change()
    Sheet1.Unprotect
    Dim i As Long
    Dim tempPrice As Double
    tempPrice = 0
    
    For i = LBound(ListBox10c.List) To UBound(ListBox10c.List)
        If ListBox10c.Selected(i) = True Then
            tempPrice = tempPrice + ListBox10c.List(i)
        End If
    Next i
    
    Range("N65").Value = tempPrice
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub ListBox13f_Change()
    Sheet1.Unprotect
    Dim i As Long
    Dim tempPrice As Double
    tempPrice = 0
    
    For i = LBound(ListBox13f.List) To UBound(ListBox13f.List)
        If ListBox13f.Selected(i) = True Then
            tempPrice = tempPrice + ListBox13f.List(i)
        End If
    Next i
    
    Range("N110").Value = tempPrice
    Call UpdatePrice
    Sheet1.Protect
End Sub

Private Sub UpdatePrice()
    Sheet1.Range("E4").Value = Sheet2.Range("B1").Value + _
                               Sheet1.ComboBox1A.Value + Sheet1.ComboBox1B.Value + Sheet1.ComboBox1C.Value + _
                               Sheet1.ComboBox2.Value + _
                               Sheet1.ComboBox3a.Value + Sheet1.ComboBox3b.Value + Sheet1.ComboBox3c.Value + Sheet1.ComboBox3d.Value + Sheet1.ComboBox3e.Value + Sheet1.ComboBox3f.Value + _
                               Sheet1.ComboBox4a.Value + Sheet1.ComboBox4b.Value + Sheet1.ComboBox4c.Value + _
                               Sheet1.ComboBox5a.Value + Sheet1.ComboBox5b.Value + _
                               Sheet1.ComboBox6.Value + _
                               Sheet1.ComboBox7a.Value + _
                               Sheet1.ComboBox8.Value + _
                               Sheet1.ComboBox9a.Value + Sheet1.ComboBox9b.Value + Sheet1.ComboBox9c.Value + Sheet1.ComboBox9d.Value + _
                               Sheet1.Range("N61").Value + Sheet1.ComboBox10b.Value + Sheet1.Range("N65").Value + Sheet1.ComboBox10d.Value + _
                               Sheet1.ComboBox11a.Value + Sheet1.ComboBox11b.Value + Sheet1.ComboBox11c.Value + Sheet1.ComboBox11d.Value + Sheet1.ComboBox11e.Value + Sheet1.ComboBox11f.Value + Sheet1.ComboBox11g.Value + Sheet1.ComboBox11h.Value + Sheet1.ComboBox11i.Value + Sheet1.ComboBox11j.Value + _
                               Sheet1.ComboBox12a.Value + Sheet1.ComboBox12b.Value + Sheet1.ComboBox12c.Value + Sheet1.ComboBox12d.Value + Sheet1.ComboBox12e.Value + Sheet1.ComboBox12f.Value + Sheet1.ComboBox12g.Value + Sheet1.ComboBox12h.Value + Sheet1.ComboBox12i.Value + Sheet1.ComboBox12j.Value + Sheet1.ComboBox12k.Value + Sheet1.ComboBox12l.Value + Sheet1.ComboBox12m.Value + Sheet1.ComboBox12n.Value + Sheet1.ComboBox12o.Value + Sheet1.ComboBox12p.Value + Sheet1.ComboBox12q.Value + Sheet1.ComboBox12r.Value + _
                               Sheet1.ComboBox13a.Value + Sheet1.ComboBox13b.Value + Sheet1.ComboBox13c.Value + Sheet1.ComboBox13d.Value + Sheet1.ComboBox13e.Value + Sheet1.Range("N110").Value + _
                               Sheet1.ComboBox14a.Value + Sheet1.ComboBox15a.Value
End Sub
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Lee

Thanks for posting the code - it's a lot to digest but I suppose I asked for that.:eek:

One thing though is that you've still not told us where it's located.

Are the controls in a worksheet? On a userform?

Where is the code you are calling located? A standard module?

I also think you really need to have a rethink on the whole thing - using that number of comboboxes just doesn't make sense.

Mind you I don't actually know what a 'configurator' is, so who am I to talk.:)
 
Upvote 0
Norie,

Yeah I was trying just to post the code where the problem was showing. At any rate, all of the controls are on the worksheet (Sheet1). When I open the visual basic editor, all of the code is located in VBAProject --> Microsoft Excel Object --> Sheet1

Here is a snapshot of part of the configurator as a visual:
config.jpg


As you can see when you change the combo box, the price to the right will update and the estimated price up top will recalculate (via the UpdatePrice function).

Lee
 
Upvote 0
Lee

I can't actually see that.:)

I really, really think you need to rethink your approach.

Have you considered using listboxes?

How about a userform?

Or perhaps Data>Validation...?

I'm pretty sure that with some other method you could avoid having to use approx 500 lines of code, some of which is pretty repetitive.:)
 
Upvote 0
Norie,

Try pulling up: http://www.netsourceinc.com/images/misc/config.jpg

There are actually a couple list boxes on the form for multiple selection options.

Well this was really my first attempt at Excel programming, so I wasn't sure where to go. I am more of a web programmer - so maybe my thought patterns were more on that side. :)

Lee
 
Upvote 0
Lee

I can see the image but that's all it is - a static picture.

So I can't see what you mean, and now you've mentioned various other things - forms, the web etc.

Are you using a userform?

Have you considered using one?
 
Upvote 0
Norie,

Sorry - yeah I was just trying to give you a visual. :)

I could use a userform but I'm not sure how I would benefit from that.

Thanks,
Lee
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
VBA to autosave and close after a set amount of time
Replies
3
Views
133
Anthony47
A
M
  • Question
Find Function VBA Named Argument Not Found-Runtime error 448
2
Replies
10
Views
321
6StringJazzer
6StringJazzer
S
  • Question
VBA Challenge
Replies
2
Views
254
SnowyBry
S
C
  • Solved
Run-time error '-2147319767 (80028029)
Replies
2
Views
712
CaptainGravyBum
C
C
  • Question
Run-time error -2147319767 (80028029) ... SOMETIMES
Replies
1
Views
82
footoo
F

Forum statistics

Threads
1,215,140
Messages
6,123,266
Members
449,093
Latest member
Vincent Khandagale

Share this page

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