MOC
05-25-2021, 09:48 AM
Hello,
I have two subs (actually three subs I guess) that I'm trying to get to work on one sheet. First, is this possible? If it is, how do I do it?
Thanks for any and all help in advance!
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strList As String
On Error Resume Next
Application.EnableEvents = False
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Not Intersect(Target, rngDV) Is Nothing Then
If Target.Validation.Type = 3 Then
strList = Target.Validation.Formula1
strList = Right(strList, Len(strList) - 1)
strDVList = strList
frmDVList.Show
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change1(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strSep As String
strSep = ", "
Application.EnableEvents = False
On Error Resume Next
If Target.Count > 1 Then GoTo exitHandler
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
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If newVal = "" Then
'do nothing
Else
If oldVal = "" Then
Target.Value = newVal
Else
Target.Value = oldVal & strSep & newVal
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)Set Target = Range("W4")
If Target.Value = "HIGH" Then
Call PostMitChoice_Initialize
End If
If Target.Value = "CATASTROPHIC" Then
Call PostMitChoice_Initialize
End If
End Sub
Private Sub PostMitChoice_Initialize()
Load PostMitChoice
PostMitChoice.Show
End Sub
I have two subs (actually three subs I guess) that I'm trying to get to work on one sheet. First, is this possible? If it is, how do I do it?
Thanks for any and all help in advance!
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strList As String
On Error Resume Next
Application.EnableEvents = False
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Not Intersect(Target, rngDV) Is Nothing Then
If Target.Validation.Type = 3 Then
strList = Target.Validation.Formula1
strList = Right(strList, Len(strList) - 1)
strDVList = strList
frmDVList.Show
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change1(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim strSep As String
strSep = ", "
Application.EnableEvents = False
On Error Resume Next
If Target.Count > 1 Then GoTo exitHandler
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
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If newVal = "" Then
'do nothing
Else
If oldVal = "" Then
Target.Value = newVal
Else
Target.Value = oldVal & strSep & newVal
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)Set Target = Range("W4")
If Target.Value = "HIGH" Then
Call PostMitChoice_Initialize
End If
If Target.Value = "CATASTROPHIC" Then
Call PostMitChoice_Initialize
End If
End Sub
Private Sub PostMitChoice_Initialize()
Load PostMitChoice
PostMitChoice.Show
End Sub