HI all,
I have a worksheet change event that I'm working on and I'm stuck. I'd like the procedure to do the following:
Only when I select from the drop-down box in cell D3 and change the value, will the procedure run. Right now, it triggers the code when I select the cell, but won't let me change anything... By default, I have ********** in the drop down. The user is expected to select either UPC or ITEM from the dropdown box triggering the below.
I have a worksheet change event that I'm working on and I'm stuck. I'd like the procedure to do the following:
Only when I select from the drop-down box in cell D3 and change the value, will the procedure run. Right now, it triggers the code when I select the cell, but won't let me change anything... By default, I have ********** in the drop down. The user is expected to select either UPC or ITEM from the dropdown box triggering the below.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Determine if change was made to cell D3
If Not Intersect(Target, Range("D3")) Is Nothing Then
Application.ScreenUpdating = False
If InStr(1, Range("D3"), "UPC") > 0 Then
'-- What I'm grabbing for the copy
Worksheets("UniqueItemList").Activate
Worksheets("UniqueItemList").Range("L5").Select
Selection.Copy
'-- Paste | Special Values To
Worksheets("Model Inputs").Activate
Worksheets("Model Inputs").Range("C24").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Worksheets("3. Analysis - IDC to RDC").Activate
Worksheets("3. Analysis - IDC to RDC").Range("B13").Select
If InStr(1, Range("D3"), "ITEM") > 0 Then
'-- What I'm grabbing for the copy based on item #
Worksheets("UniqueItemList").Activate
Worksheets("UniqueItemList").Range("M5").Select
Selection.Copy
'-- Paste | Special Values To
Worksheets("Model Inputs").Activate
Worksheets("Model Inputs").Range("C24").Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'-- Just selecting a differeent cell to switch too after this runs
Worksheets("3. Analysis - IDC to RDC").Activate
Worksheets("3. Analysis - IDC to RDC").Range("B13").Select
Else
Call MessageBox
Application.ScreenUpdating = True
End If
End Sub
Sub MessageBox_vbDefaultButton1()
'Variable Declaration
Dim OutPut As Integer
'Example of vbDefaultButton1
OutPut = MsgBox("You must select a value in cell D3", vbOKOnly, "Selection Required")
End Sub
Last edited: