wisemang
03-14-2012, 07:16 AM
Please be kind as this is first ever post.
I am a total beginner with VBA code and have managed to write the following but struggling to insert a message box at a certain point within the code below :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If ActiveCell.Column = 6 Then
Target.Value = ActiveCell.Value
Application.EnableEvents = False
Select Case Target
Case "Pending"
ActiveCell.Value = "Test In Progress"
' icolor = 3
' cel = 1
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 192 'red
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case "Test In Progress"
ActiveCell.Value = "Passed Testing"
' icolor = 40
' cel = 2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274 'bright green
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case "Passed Testing"
ActiveCell.Value = "Schedule Live Date"
' icolor = 40
' cel = 2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407 'orange
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case "Schedule Live Date"
ActiveCell.Value = "LIVE"
' icolor = 46
' cel = 1
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936 'green
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case "LIVE"
ActiveCell.Value = "Cancelled"
' icolor = 5
' cel = 1
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10498160 'light green
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case "Cancelled"
ActiveCell.Value = "Completed"
' icolor = 48
' cel = 1
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255 'White
.TintAndShade = 1
.PatternTintAndShade = 1
End With
Case "Completed"
ActiveCell.Value = "Pending"
' icolor = 40
' cel = 2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274 'bright green
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case Else
' Then clear the background
Target.Interior.ColorIndex = xlNone
Target.Value = "Pending"
End Select
Application.EnableEvents = True
' This is to prevent the cell from being edited when double-clicked
Cancel = True
End If
End Sub
What i am trying to do is when the cell gets to 'COMPLETED' i want a message box to pop up to ask ''Do you want to archive this row to Sheet 2'' yes or no.
If yes then removes from sheet 1 and places on sheet 2. If no then reverts back to ''Case Pending''
Many thanks in advance
I am a total beginner with VBA code and have managed to write the following but struggling to insert a message box at a certain point within the code below :
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If ActiveCell.Column = 6 Then
Target.Value = ActiveCell.Value
Application.EnableEvents = False
Select Case Target
Case "Pending"
ActiveCell.Value = "Test In Progress"
' icolor = 3
' cel = 1
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 192 'red
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case "Test In Progress"
ActiveCell.Value = "Passed Testing"
' icolor = 40
' cel = 2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274 'bright green
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case "Passed Testing"
ActiveCell.Value = "Schedule Live Date"
' icolor = 40
' cel = 2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407 'orange
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case "Schedule Live Date"
ActiveCell.Value = "LIVE"
' icolor = 46
' cel = 1
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936 'green
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case "LIVE"
ActiveCell.Value = "Cancelled"
' icolor = 5
' cel = 1
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 10498160 'light green
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case "Cancelled"
ActiveCell.Value = "Completed"
' icolor = 48
' cel = 1
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255 'White
.TintAndShade = 1
.PatternTintAndShade = 1
End With
Case "Completed"
ActiveCell.Value = "Pending"
' icolor = 40
' cel = 2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274 'bright green
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Case Else
' Then clear the background
Target.Interior.ColorIndex = xlNone
Target.Value = "Pending"
End Select
Application.EnableEvents = True
' This is to prevent the cell from being edited when double-clicked
Cancel = True
End If
End Sub
What i am trying to do is when the cell gets to 'COMPLETED' i want a message box to pop up to ask ''Do you want to archive this row to Sheet 2'' yes or no.
If yes then removes from sheet 1 and places on sheet 2. If no then reverts back to ''Case Pending''
Many thanks in advance