Hello!
It appears that I have a similar question and I'm getting Stuck! I would appreciate a little help on this!
I have a Workbook that has a number of Worksheets that once a particular number of cells are selected/Fill the user can then click a CSV button that generates a CSV file that they will upload into JIRA to automatically created a number of JIRA Issues. I have the code for the CSV and works great! In the cells that need to be selected/filled I have three Drop down lists. This makes sure my users are nothing adding random things and everything is uniformed for sorting purposes. I'm able to achieve this via a Change Event and I get it to work on one event. I would like to have all three Change Events. I attempted but I'm getting stuck! Help! Below are the three Change Events that I would like to use. Two are very simple but the third one is a big more involved and I think that's where I get stuck.
Change Event 1
Change Event 2
Change Event 3 -
It appears that I have a similar question and I'm getting Stuck! I would appreciate a little help on this!
I have a Workbook that has a number of Worksheets that once a particular number of cells are selected/Fill the user can then click a CSV button that generates a CSV file that they will upload into JIRA to automatically created a number of JIRA Issues. I have the code for the CSV and works great! In the cells that need to be selected/filled I have three Drop down lists. This makes sure my users are nothing adding random things and everything is uniformed for sorting purposes. I'm able to achieve this via a Change Event and I get it to work on one event. I would like to have all three Change Events. I attempted but I'm getting stuck! Help! Below are the three Change Events that I would like to use. Two are very simple but the third one is a big more involved and I think that's where I get stuck.
Change Event 1
VBA Code:
' DropDown_ProductID
ProductName = Target.Value
If Target.Column = 10 Then
ProductCode = Application.VLookup(ProductName, ActiveSheet.Range("ProductID"), 2, False)
If Not IsError(ProductCode) Then
Target.Value = ProductCode
End If
End If
Change Event 2
VBA Code:
' DropDown_StaffName
StaffName = Target.Value
If Target.Column = 8 Then
StaffTest = Application.VLookup(StaffName, ActiveSheet.Range("StaffName"), 2, False)
If Not IsError(StaffTest) Then
Target.Value = StaffTest
End If
End If
End Sub
Change Event 3 -
VBA Code:
'Multple Section
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lOld As Long
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 9 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lOld = Len(oldVal)
If Left(newVal, lOld) = oldVal Then
Target.Value = newVal
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
End If
If newVal = "" Then
'do nothing
Else
lOld = Len(oldVal)
If Left(newVal, lOld) = oldVal Then
Target.Value = newVal
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub