Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help.. lost in code.
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 -- MarcB ------------------------------------------------------------------------ MarcB's Profile: http://www.excelforum.com/member.php...o&userid=14988 View this thread: http://www.excelforum.com/showthread...hreadid=278300 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help.. lost in code.
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 -- anilsolipura ----------------------------------------------------------------------- anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627 View this thread: http://www.excelforum.com/showthread.php?threadid=27830 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Please help.. lost in code.
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 "MarcB" wrote: 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 -- MarcB ------------------------------------------------------------------------ MarcB's Profile: http://www.excelforum.com/member.php...o&userid=14988 View this thread: http://www.excelforum.com/showthread...hreadid=278300 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lost in code | Excel Discussion (Misc queries) | |||
Help! Lost sheets by using View Code on worksheet tab | Excel Discussion (Misc queries) | |||
adding a code to calculate how much time is lost | Excel Discussion (Misc queries) | |||
VBA code gets lost from workbook if I paste in new data and save??? | Excel Programming | |||
code lost after saving | Excel Programming |