Please help.. lost in code.

[MarcB]

I entered the code below and its not working. I know I'm missing
something easy out, perhaps I have it in the wrong place, I'm just not
sure. I have the code in the actual worksheet code box in vba. I know
I'm leaving something out and I'm sure it has to do with the
ComboBox1_Change() startup. What I have is a drop-down list of
companies, about 75. Its on a Sheet labeled "Input", there is an
"Other" item in the list. The user will select either other or one of
the companies. This relates to the "quote" sheet. On the contract
sheet, if the user selects ANY of the companies it will say "this quote
is provided in accordance to the contract already signed." if they
select Other from the list, I would like a jpg of a generic contract to
pop up at the end of the contract, in cell B50.

I've created my drop-down list and it works fine, but I'm not sure what
the best method is to complete the quote. I know that the code you gave
me, anilsolipuram, is where I need to be. Below I have copied and
pasted my code, I'm just not sure how to activate it or maybe I'm
naming it wrong somewhere.

Private Sub ComboBox1_Change()

Dim pic As Object
Dim t As String

If ComboBox1.Value = "Other" Then
Sheets("Quote").Select
ActiveSheet.Range("B50").Select
ActiveSheet.Range("B50").Value = ""
Set pic = ActiveSheet.Pictures.Insert("K:\Bids & Proposals Department
Folder\Bids\Quick Quote Calculator\Image\tocstandard.jpg")
pic.Width = ActiveSheet.Range("B50").Width
pic.Height = ActiveSheet.Range("B50").Height
pic.Left = ActiveSheet.Range("B50").Left
pic.Top = ActiveSheet.Range("B50").Top
t = pic.Name
Application.CommandBars("Picture").Visible = False

Else
Sheets("Quote").Select
If t <> "" Then
ActiveSheet.Shapes(t).Select
ActiveSheet.Shapes(t).Delete
t = ""
End If

ActiveSheet.Range("B50").Select

ActiveSheet.Range("B50").Value = ""
ActiveSheet.Range("B50").Value = "This agreement provided as per the
conditions in your current contract."
End If
End Sub

 

[anilsolipuram]

I used visual basic toolbar to add combo box to excel.

go to view->toolbars->visual basic ->click "control toolbox" from th
popup window.->add combo box from the control toolbox.


you can add list data into combo box by right click the comb
box->properties-> filllistrange ex a1:a4 (values for list)

now double click the combo box, it will open up the vba code

Private Sub ComboBox1_Change()

End Sub

just paste my code there

 

[Guest]

Your t variable went out of scope each time you changed the combo value.
It would always be null.

Put this in the code module of the sheet contianing the combo. Hope this
helps....(I wasn't entirely sure what sheets you were chaning..so you may
have to tweak it)

noel

Private Sub ComboBox1_Change()

Dim pic As Object
Dim ws_Quote As Worksheet
Const myPicName As String = "AnyNameHere"

Set ws_Quote = Worksheets("Quote")
With ws_Quote
If ComboBox1.Value = "Other" Then
.Range("B50").Value = ""
Set pic = .Pictures.Insert("H:\clientMoney\Ms.jpg") '"K:\Bids &
Proposals Department Folder\Bids\Quick Quote
Calculator\Image\tocstandard.jpg")
pic.Name = myPicName
pic.Width = .Range("B50").Width
pic.Height = .Range("B50").Height
pic.Left = .Range("B50").Left
pic.Top = .Range("B50").Top
Application.CommandBars("Picture").Visible = False
Else
On Error Resume Next
If VarPtr(.Shapes(myPicName)) <> 0 Then
..Shapes(myPicName).Delete
On Error GoTo 0
.Range("B50").Value = "This agreement provided as per the
conditions in your current contract."
End If
End With
End Sub