PDA

View Full Version : [SOLVED] Combining multiple range as target cell



jammer6_9
08-12-2013, 12:04 AM
As of now I am still :dunno in adding another range as my target. Below code works perfect however I have tried to set and add below range as myTarget range. Can anyone help me on this?: pray2:

i1:i54
k1:k54
m1:m54
o1:o54
q1:q54



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rMyRg As Range
On Error Resume Next
Set rMyRg = Range("g1:g54")
Set rMyRg = Application.Intersect(rMyRg, Target)
If Not rMyRg Is Nothing Then
If ActiveCell.Address = "" Then
Else
ActiveCell.Offset(0, -1).Select
ActiveCell.ClearContents
If frmCalendarTA.Visible = False Then
frmCalendarTA.Show
ActiveCell.Offset(1, 1).Select
End If
End If
End If
End Sub

Aussiebear
08-12-2013, 02:00 AM
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rMyRg As Range
On Error Resume Next
Set rMyRg = Range("g1:q54")
Set rMyRg = Application.Intersect(rMyRg, Target)
If Not rMyRg Is Nothing Then
If ActiveCell.Address = "" Then
Else
ActiveCell.Offset(0, -1).ClearContents
If frmCalendarTA.Visible = False Then
frmCalendarTA.Show
ActiveCell.Offset(1, 1).Select
End If
End If
End If
End Sub

p45cal
08-12-2013, 02:42 AM
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rMyRg As Range
On Error Resume Next
Set rMyRg = Range("g1:g54, i1:i54, k1: k54, m1: m54, o1: o54, q1: q54")
Set rMyRg = Application.Intersect(rMyRg, Target)
If Not rMyRg Is Nothing Then
If ActiveCell.Address = "" Then
Else
ActiveCell.Offset(0, -1).Select
ActiveCell.ClearContents
If frmCalendarTA.Visible = False Then
frmCalendarTA.Show
ActiveCell.Offset(1, 1).Select
End If
End If
End If
End Sub
but the line:
If ActiveCell.Address = "" Then will never be true because an active cell always has an address. Do you mean ActiveCell.Value?

jammer6_9
08-12-2013, 03:00 AM
Thank you! Yes I have amended it also into Activecell.Value...



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rMyRg As Range
On Error Resume Next
Set rMyRg = Range("g1:g54, i1:i54, k1: k54, m1: m54, o1: o54, q1: q54")
Set rMyRg = Application.Intersect(rMyRg, Target)
If Not rMyRg Is Nothing Then
If ActiveCell.Address = "" Then
Else
ActiveCell.Offset(0, -1).Select
ActiveCell.ClearContents
If frmCalendarTA.Visible = False Then
frmCalendarTA.Show
ActiveCell.Offset(1, 1).Select
End If
End If
End If
End Sub
but the line:
If ActiveCell.Address = "" Then will never be true because an active cell always has an address. Do you mean ActiveCell.Value?