two target as range on same worksheet

cknnugget

New Member
Joined
Jun 29, 2020
Messages
44
Office Version
  1. 365
Platform
  1. Windows
I have 2 worksheet changes that need to be on the same worksheet. They work independently from each other. Separate they work fine; however I am not sure how to have them work on the same sheet. Is it possible to have two on the same worksheet?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SBItotarget As Range
Set SBItotarget = Range("C5")
If Intersect(Target, SBItotarget) Is Nothing Then Exit Sub
If Not IsEmpty(SBItotarget) Then
ThisWorkbook.Connections("Query - MEMOLog").Refresh

End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim celltotarget As Range
Dim celltodate As Range
Dim Cellstodate As Range
Dim UN As String
UN = Environ$("UserName")
Set celltotarget = Range("F20")
Set celltodate = Range("C20")
Set Cellstodate = Range("F22")
If Intersect(Target, celltotarget) Is Nothing Then Exit Sub
If celltodate.Value = "" Then
Sheets(Sheet1.Name).Unprotect Password:=""
celltodate.Value = Now
Cellstodate.Value = Now
Sheets(Sheet1.Name).Protect Password:=""
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
have you tried having them both together to see what happens? You might need to combine them, save and reopen before the VBA takes affect.


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SBItotarget As Range
Set SBItotarget = Range("C5")
If Intersect(Target, SBItotarget) Is Nothing Then Exit Sub
If Not IsEmpty(SBItotarget) Then
ThisWorkbook.Connections("Query - MEMOLog").Refresh

End If

Dim celltotarget As Range
Dim celltodate As Range
Dim Cellstodate As Range
Dim UN As String
UN = Environ$("UserName")
Set celltotarget = Range("F20")
Set celltodate = Range("C20")
Set Cellstodate = Range("F22")
If Intersect(Target, celltotarget) Is Nothing Then Exit Sub
If celltodate.Value = "" Then
Sheets(Sheet1.Name).Unprotect Password:=""
celltodate.Value = Now
Cellstodate.Value = Now
Sheets(Sheet1.Name).Protect Password:=""

end sub
 
Upvote 0
Solution
have you tried having them both together to see what happens? You might need to combine them, save and reopen before the VBA takes affect.


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SBItotarget As Range
Set SBItotarget = Range("C5")
If Intersect(Target, SBItotarget) Is Nothing Then goto Nexttask
If Not IsEmpty(SBItotarget) Then
ThisWorkbook.Connections("Query - MEMOLog").Refresh

End If
[QUOTE]
Nexttask:
[/QUOTE]
Dim celltotarget As Range
Dim celltodate As Range
Dim Cellstodate As Range
Dim UN As String
UN = Environ$("UserName")
Set celltotarget = Range("F20")
Set celltodate = Range("C20")
Set Cellstodate = Range("F22")
If Intersect(Target, celltotarget) Is Nothing Then Exit Sub
If celltodate.Value = "" Then
Sheets(Sheet1.Name).Unprotect Password:=""
celltodate.Value = Now
Cellstodate.Value = Now
Sheets(Sheet1.Name).Protect Password:=""

end sub
Very close I changed the first Exit sub to a goto to start the next task and it worked perfectly. Thank you for the assistance.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top