Is this correct: Dim rng1 As Range Set rng1 = Range("F6:F206") ' Check if the changed…
- User
Is this correct: Dim rng1 As Range Set rng1 = Range("F6:F206") ' Check if the changed cells are within the specified range If Not Intersect(Target, rng1) Is Nothing Then If Target.Value = "" Then Exit Sub Else MsgBox "When entering the Return Date" & vbNewLine & "" & vbNewLine & _ "Always Enter Actual Date of Return", vbInformation, "RETURN DATE" End If
- Assistant
Your code snippet appears to be a part of a VBA (Visual Basic for Applications) routine, likely designed to respond to changes in a specific range of cells. You have the right idea, but there are a couple of issues and improvements that can be made.
-
Variable Declaration for Target: The
Targetvariable is usually declared when you're working within event procedures (likeWorksheet_Change). Ensure that you have the correct context or variable declaration forTarget. -
Logical Structure: The logic within the
Ifblock needs a small adjustment, particularly for theElseclause. It seems like the message box should only display if the cell is not empty.
Here is a corrected version of your code snippet, assuming it is within a
Worksheet_Changeevent:Private Sub Worksheet_Change(ByVal Target As Range) Dim rng1 As Range Set rng1 = Range("F6:F206") ' Check if the changed cells are within the specified range If Not Intersect(Target, rng1) Is Nothing Then If Target.Value = "" Then Exit Sub Else MsgBox "When entering the Return Date" & vbNewLine & vbNewLine & _ "Always Enter Actual Date of Return", vbInformation, "RETURN DATE" End If End If End SubKey Points:
-
Event Handler: Make sure this code is placed in the appropriate worksheet code module (e.g.,
Sheet1,Sheet2) and that it is an event procedure. This will allow it to respond when changes are made to the worksheet. -
Exit Sub: The
Exit Sub, whenTarget.Valueis empty, will effectively stop further execution if a cell withinrng1is cleared. -
Message Box: The message box will now display only if the changed cell in
rng1contains a value after an edit, which fits the desired logic.
Make sure the rest of your VBA project (including
Option Explicitand other relevant procedures) is set up correctly for best practices and to avoid runtime errors. -
- User
I have an error 'Method Range of object '_Worksheet' failed. on this line: 'If Not Intersect(Target, Range(rangee(e))) Is Nothing Then' In the code below.
Option Explicit
Private Sub Worksheet_Activate() ThisWorkbook.Worksheets("HolCard").Unprotect Password:="edit" Application.Calculate ThisWorkbook.Worksheets("HolCard").Protect Password:="edit" End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, rng5 As Range, rng6 As Range, rng7 As Range Set rng1 = Range("B3:B23") Set rng2 = Range("E3:E23") Set rng3 = Range("H3:H23") Set rng4 = Range("K3:K23") Set rng5 = Range("N3:N23") Set rng6 = Range("Q3:Q23") Set rng7 = Range("T3:T23") ' Check if the changed cells are within the specified range If Not Intersect(Target, rng1) Is Nothing Then Range("C3:C23").Calculate End If If Not Intersect(Target, rng2) Is Nothing Then Range("F3:F23").Calculate End If If Not Intersect(Target, rng3) Is Nothing Then Range("I3:I23").Calculate End If If Not Intersect(Target, rng4) Is Nothing Then Range("L3:L23").Calculate End If If Not Intersect(Target, rng5) Is Nothing Then Range("O3:O23").Calculate End If If Not Intersect(Target, rng6) Is Nothing Then Range("R3:R23").Calculate End If If Not Intersect(Target, rng7) Is Nothing Then Range("U3:U23").Calculate End If
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Count = 1 And Target.Column = 1 And Target.Row >= 3 And Target.Row <= 23 Then If IsEmpty(Target) Then Exit Sub If Not Target.Comment Is Nothing Then MsgBox "Date Already Recorded to Leave" Exit Sub Else Call Module5.ProcessLeaveRequestABC(Target) Cancel = True End If End If
If Target.Count = 1 And Target.Column = 4 And Target.Row >= 3 And Target.Row <= 23 Then If IsEmpty(Target) Then Exit Sub If Not Target.Comment Is Nothing Then MsgBox "Date Already Recorded to Leave" Exit Sub Else Call Module5.ProcessLeaveRequestDEF(Target) Cancel = True End IfEnd If
If Target.Count = 1 And Target.Column = 7 And Target.Row >= 3 And Target.Row <= 23 Then If IsEmpty(Target) Then Exit Sub If Not Target.Comment Is Nothing Then MsgBox "Date Already Recorded to Leave" Exit Sub Else Call Module5.ProcessLeaveRequestGHI(Target) Cancel = True End IfEnd If
If Target.Count = 1 And Target.Column = 10 And Target.Row >= 3 And Target.Row <= 23 Then If IsEmpty(Target) Then Exit Sub If Not Target.Comment Is Nothing Then MsgBox "Date Already Recorded to Leave" Exit Sub Else Call Module5.ProcessLeaveRequestJKL(Target) Cancel = True End IfEnd If
If Target.Count = 1 And Target.Column = 13 And Target.Row >= 3 And Target.Row <= 23 Then If IsEmpty(Target) Then Exit Sub If Not Target.Comment Is Nothing Then MsgBox "Date Already Recorded to Leave" Exit Sub Else Call Module5.ProcessLeaveRequestMNO(Target) Cancel = True End IfEnd If
If Target.Count = 1 And Target.Column = 16 And Target.Row >= 3 And Target.Row <= 23 Then If IsEmpty(Target) Then Exit Sub If Not Target.Comment Is Nothing Then MsgBox "Date Already Recorded to Leave" Exit Sub Else Call Module5.ProcessLeaveRequestPQR(Target) Cancel = True End IfEnd If
If Target.Count = 1 And Target.Column = 19 And Target.Row >= 3 And Target.Row <= 23 Then If IsEmpty(Target) Then Exit Sub If Not Target.Comment Is Nothing Then MsgBox "Date Already Recorded to Leave" Exit Sub Else Call Module5.ProcessLeaveRequestSTU(Target) Cancel = True End IfEnd If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count > 1 Then Application.EnableEvents = True Exit Sub End If ' DATE END CHECK FOR WEEKEND OR PUBLIC HOLIDAY Dim rng As Range Set rng = Union(Range("B3:B23"), Range("E3:E23"), Range("H3:H23"), Range("K3:K23"), Range("N3:N23"), Range("Q3:Q23"), Range("T3:T23")) If Not Intersect(Target, rng) Is Nothing Then If Target.Offset(0, -1).Value = "" Then Application.Undo MsgBox "Date Start must be entered first", vbCritical, "DATE START" End If End If Dim rangee() As Variant rangee = Array(Range("B3:B23"), Range("E3:E23"), Range("H3:H23"), Range("K3:K23"), Range("N3:N23"), Range("Q3:Q23"), Range("T3:T23")) Dim e As Long Dim foundeMatch As Boolean foundeMatch = False'Check if Target intersects with any range in the array For e = LBound(rangee) To UBound(rangee) If Not Intersect(Target, Range(rangee(e))) Is Nothing Then foundeMatch = True Exit For 'Exit the loop once a match is found End If Next e
'If no match found, exit the subroutine If Not foundeMatch Then Application.EnableEvents = True Exit Sub End If
'If a match is found, process the rest of the code For e = LBound(rangee) To UBound(rangee) If Not Intersect(Target, Range(rangee(e))) Is Nothing Then Dim dateEnd As Date dateEnd = Target.Value If Target.Value <> "" Then Call Module4.HolPubDateCheckEnd(dateEnd) Exit For 'Exit the loop once a match is found End If End If Next e
' DATE START CHECK FOR WEEKEND OR PUBLIC HOLIDAY Dim ranges() As Variant ranges = Array("A3:A23", "D3:D23", "G3:G23", "J3:J23", "M3:M23", "P3:P23", "S3:S23") Dim s As Long Dim foundsMatch As Boolean foundsMatch = False'Check if Target intersects with any range in the array For s = LBound(ranges) To UBound(ranges) If Not Intersect(Target, Range(ranges(s))) Is Nothing Then foundsMatch = True Exit For 'Exit the loop once a match is found End If Next s
'If no match found, exit the subroutine If Not foundsMatch Then Application.EnableEvents = True Exit Sub End If
'If a match is found, process the rest of the code For s = LBound(ranges) To UBound(ranges) If Not Intersect(Target, Range(ranges(s))) Is Nothing Then Dim dateStart As Date dateStart = Target.Value If Target.Value <> "" Then Call Module4.HolPubDateCheckStart(dateStart) Exit For 'Exit the loop once a match is found End If End If Next s Application.EnableEvents = True
End Sub
- Assistant
The error you're encountering, "Method Range of object '_Worksheet' failed," occurs when you're trying to access a
Rangeobject incorrectly. In the relevant part of your code, you're attempting to use theRangemethod on an array ofRangeobjects, which is not correct.Problematic Code
The problematic section is this part of your
Worksheet_Changesubroutine:Dim rangee() As Variant rangee = Array(Range("B3:B23"), Range("E3:E23"), Range("H3:H23"), Range("K3:K23"), Range("N3:N23"), Range("Q3:Q23"), Range("T3:T23"))Correcting the Code
Instead of creating an array of
Rangeobjects directly using theRangemethod, you can simply use the addresses as strings in the array. Here's how you can define therangeearray correctly:Dim rangee() As String rangee = Array("B3:B23", "E3:E23", "H3:H23", "K3:K23", "N3:N23", "Q3:Q23", "T3:T23")Then, when you want to check the intersection, you can use it like this:
For e = LBound(rangee) To UBound(rangee) If Not Intersect(Target, Me.Range(rangee(e))) Is Nothing Then foundeMatch = True Exit For 'Exit the loop once a match is found End If Next eComplete Corrected Block
Here's the corrected block for the
Worksheet_Changesubroutine including just the relevant parts associated withrangee:Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Count > 1 Then Application.EnableEvents = True Exit Sub End If ' DATE END CHECK FOR WEEKEND OR PUBLIC HOLIDAY Dim rng As Range Set rng = Union(Range("B3:B23"), Range("E3:E23"), Range("H3:H23"), Range("K3:K23"), Range("N3:N23"), Range("Q3:Q23"), Range("T3:T23")) If Not Intersect(Target, rng) Is Nothing Then If Target.Offset(0, -1).Value = "" Then Application.Undo MsgBox "Date Start must be entered first", vbCritical, "DATE START" End If End If Dim rangee() As String rangee = Array("B3:B23", "E3:E23", "H3:H23", "K3:K23", "N3:N23", "Q3:Q23", "T3:T23") Dim e As Long Dim foundeMatch As Boolean foundeMatch = False ' Check if Target intersects with any range in the array For e = LBound(rangee) To UBound(rangee) If Not Intersect(Target, Me.Range(rangee(e))) Is Nothing Then foundeMatch = True Exit For ' Exit the loop once a match is found End If Next e ' If no match found, exit the subroutine If Not foundeMatch Then Application.EnableEvents = True Exit Sub End If ' If a match is found, process the rest of the code For e = LBound(rangee) To UBound(rangee) If Not Intersect(Target, Me.Range(rangee(e))) Is Nothing Then Dim dateEnd As Date dateEnd = Target.Value If Target.Value <> "" Then Call Module4.HolPubDateCheckEnd(dateEnd) Exit For 'Exit the loop once a match is found End If End If Next e ' DATE START CHECK FOR WEEKEND OR PUBLIC HOLIDAY Dim ranges() As String ranges = Array("A3:A23", "D3:D23", "G3:G23", "J3:J23", "M3:M23", "P3:P23", "S3:S23") Dim s As Long Dim foundsMatch As Boolean foundsMatch = False ' Check if Target intersects with any range in the array For s = LBound(ranges) To UBound(ranges) If Not Intersect(Target, Me.Range(ranges(s))) Is Nothing Then foundsMatch = True Exit For ' Exit the loop once a match is found End If Next s ' If no match found, exit the subroutine If Not foundsMatch Then Application.EnableEvents = True Exit Sub End If ' If a match is found, process the rest of the code For s = LBound(ranges) To UBound(ranges) If Not Intersect(Target, Me.Range(ranges(s))) Is Nothing Then Dim dateStart As Date dateStart = Target.Value If Target.Value <> "" Then Call Module4.HolPubDateCheckStart(dateStart) Exit For ' Exit the loop once a match is found End If End If Next s Application.EnableEvents = True End SubSummary
By using string addresses in your
rangeearray, you correctly specify the ranges for theIntersectmethod. Additionally, includingMe.beforeRangeensures that you're accessing ranges within the current worksheet. This should resolve your issue and avoid run-time errors.