Greetings!
I am trying to merge the following worksheet change subs into one VBA and am so far unsuccessful - I can get the first three to work, but the 4th section "'thow input box if GM type = DS or RDM" won't fire work when combined into one.
Any ideas?
Many thanks in advance!!!
First section:
Second section:
I am trying to merge the following worksheet change subs into one VBA and am so far unsuccessful - I can get the first three to work, but the 4th section "'thow input box if GM type = DS or RDM" won't fire work when combined into one.
Any ideas?
Many thanks in advance!!!
First section:
Code:
'main VBA
Private Sub Worksheet_Change(ByVal Target As Range)
'set date field automatically once user is selected in row 1
Dim yourdate As String
yourdate = Format(Date, "yyyy-mm-dd")
If Target.Column >= 1 And Target.Column <= 1 Then
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Cells(Target.Row, 2) = (yourdate)
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End If
'validate drive letter or mount point but not both
If Target.Row >= 5 <= 105 Then
If (Target.Column = 15) Then
If (Me.Range("Q" & Target.Row).Value <> "") Then
UndoValue
End If
ElseIf (Target.Column = 17) Then
If (Me.Range("O" & Target.Row).Value <> "") Then
UndoValue
End If
End If
End If
'change SCSI id field to 'NA' based on GM role
If Intersect(Target, Range("R5:R55")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then
Me.Range("M" & Target.Row) = ""
Exit Sub
End If
With Application
.EnableEvents = False
.ScreenUpdating = False
Select Case Target.Value
Case "H1", "H2", "J2"
Me.Range("M" & Target.Row).Value = Application.RandBetween(1, 99)
Case "I2"
Me.Range("M" & Target.Row) = "NA"
End Select
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
'sub to undo incorrect value
Private Sub UndoValue()
With Application
.EnableEvents = False
.ScreenUpdating = False
Application.Undo
.EnableEvents = True
.ScreenUpdating = True
End With
MsgBox "Please choose a drive letter OR a mount point, not both.", vbExclamation
End Sub
Second section:
Code:
'main VBA
Private Sub Worksheet_Change(ByVal Target As Range)
'thow input box if GM type = DS or RDM
Dim mynum As Integer
On Error GoTo errHandler:
If Target.Row > 4 And Target.Row < 106 And Target.Column = 11 Then
If Target.Value = "DS" Then
mynum = InputBox( _
"You have selected a LUN type that may require a series identifier (e.g. DS1, 2, 3, 3, etc.). If not, please click Cancel.", _
"LUN Series Number ...", _
"Assign a series number? If so please enter a value between 1 - 200.")
If mynum > 200 Or mynum < 1 Then
MsgBox "Invalid Entry - Enter a value between 1 - 200", vbExclamation
Exit Sub
End If
Cells(Target.Row, Target.Column + 1) = mynum
ElseIf Target.Value = "RDM" Then
mynum = InputBox( _
"You have selected a LUN type that may require a series identifier (e.g. RDM1, 2, 3, 3, etc.) If not, please click Cancel.", _
"LUN Series Number ...", _
"Assign a series number? If so please enter a value between 1 - 200.")
If mynum > 200 Or mynum < 1 Then
MsgBox "Invalid Entry - Enter a value between 1 - 200.", vbExclamation
Exit Sub
End If
Cells(Target.Row, Target.Column + 1) = mynum
End If
End If
Exit Sub
errHandler:
MsgBox "No LUN Series Number Chosen.", vbInformation
End Sub