Solved: Program for combo box is overwriting formula in cell

[slondon]

I am using a combo box on the "Drug Costs" worksheet to allow the user to select AWP or MAC. Based on their choice, some cells should turn grey and be locked and others should turn white and be reset to default values that reside on another worksheet in the workbook "Drug Costs Worksheet". This seems to be working fine for 9 of my 10 combo boxes. However, for the 10th combo box, every time I click on it, it pastes one of these default values into cell D29 on the "Drug Costs Worksheet" and overwrites a link that I want in that cell. I can't figure out why it is doing this and was hoping someone out there might be able to point me in the right direction.

Any help would be much appreciated as I am supposed to have this file working by Monday AM.
Thanks!

Here is my program:


Private Sub BasePrice4_Change()
'This section sets MAC to grey if AWP is selected.
If BasePrice4.Value = Worksheets("Drug Costs Worksheet").Range("b5").Value Then
'
Worksheets("Drug Costs").Range("G9").Value = Worksheets("Drug Costs Worksheet").Range("k29").Value
Worksheets("Drug Costs").Range("i9").Value = Worksheets("Drug Costs Worksheet").Range("l29").Value
Worksheets("Drug Costs").Range("k9").Value = Worksheets("Drug Costs Worksheet").Range("m29").Value
'
Worksheets("Drug Costs").Range("G9").Select
Selection.Locked = False
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
'
'
Worksheets("Drug Costs").Range("i9").Select
Selection.Locked = True
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
'
'
'This section sets AWP to grey if MAC is selected
If BasePrice4.Value = Worksheets("Drug Costs Worksheet").Range("b6").Value Then
'
Worksheets("Drug Costs").Range("G9").Value = Worksheets("Drug Costs Worksheet").Range("r29").Value
Worksheets("Drug Costs").Range("i9").Value = Worksheets("Drug Costs Worksheet").Range("s29").Value
Worksheets("Drug Costs").Range("k9").Value = Worksheets("Drug Costs Worksheet").Range("t29").Value
Worksheets("Drug Costs").Range("G9").Select
Selection.Locked = True
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
'
'
Worksheets("Drug Costs").Range("i9").Select
'Selection.Locked = False
With Selection.Interior
.ColorIndex = 2
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End If
'
End Sub

[OBP]

This may be as a result of an error occurring, which means when Excel tries to paste it will paste the last thing in the "clipboard". You do not have any error handling routines in your code to tell you if an error is occurring or not.
Perhaps you should put one in just to be on the safe side.

[johnske]

Hi slondon,

I can see nothing in the code you have above that would cause this. A work-around to meet your deadline would be to write the link anew just before the End Sub i.e. your penultimate line would be of this form...
'put your own link on the RHS of the = sign
Worksheets("Drug Costs Worksheet").Range("D29") _
= "='" & ActiveWorkbook.Path & "\[SourceBook.xls]Sheet1'!A1"

[slondon]

Thanks for your help. I used the work around of adding the last line of text and it solved the problem and helped me meet my deadline. Thanks again!