Apologies for the basic question, but how would I tweak this so that all the code works when on the same sheet? I'm unsure what to do when Private Sub Worksheet_SelectionChange(ByVal Target As Range) appears in both pieces of code?
Many thanks
Please Login or Register to view this content.
Hi,
It's not clear, (at least to me) exactly what you are getting at here. Are these really two Sheet procedures or two procedures that you've written in a Standard Module? Neither am I clear as to what 'on the same sheet' means.
What is the overall aim. Perhaps if you can explain that we may be able to offer a solution.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi Richard,
Sorry, I didn't make that very clear... Okay, so I have two different pieces of code both doing different things:
Code 1
Code 2Please Login or Register to view this content.
Both need to sit on Sheet 1 but I don't know how to combine the two because both use 'Private Sub Worksheet_Change(ByVal Target As Range)'Please Login or Register to view this content.
Hope that makes a bit more sense.
Many thanks
Hi,
They don't both use the Worksheet Change event. One uses the Worksheet Selection Change event. The two events are subtly different.
In addition your Code 2 comprises both a Sheet change and a Sheet Selection change.
What events are you trying to detect and in what circumstances?
But there is an instance of
in both...Please Login or Register to view this content.
So, Code 1 basically displays the value of the selected cell in another cell and Code 2 records a log of any chnages made to the sheet. Both pieces of code work individually, but they both need to sit on Sheet 1 and they conflict, i.e if they both appear together and the code runs, I get a Compile Error which points to the second instance of
Third line from the bottom in Code 2. So I'm trying to understand how I can combine both Code 1 and Code 2 to work in harmony. Sorry I'm still very new to VBA...Please Login or Register to view this content.
Hi,
Still not sure exactly what you're trying to trap but if you're wanting to detect whether a cell changes and then record the fact of the change and what the change is then the way I normally do it is as follows
Please Login or Register to view this content.
Not exactly...
Basically I already had Code 1 sitting on Sheet 1, all working as expected. Then I also needed some separate code to do a different task, Code 2, this also needs to sit on Sheet 1, but if I put both Code 1 and Code 2 on Sheet 1 at the same time I get a compile error because there are two instances of
One in Code 1 and another in Code 2, so I'm trying to understand how Code 1 and Code 2 can co-exisit on Sheet 1 without the compile error.Please Login or Register to view this content.
Are you manually writing this Selection Change event as another procedure rather than using the sheet selection change itself.
Perhaps this would be clearer if you were to upload the workbook so that we can see the problem in context.
Just put the code in the third of the OP proceedures as the last line of the Selection_Change event.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Hi Richard,
Sample attached. On Sheet1 both 'pieces' of code are included.
Code 1 at the top displays the value of the selected cell in cell S1 and then only allows the user to enter the same value as currently appears in columns F:J and L:M. If the user inserts a new row they can then only enter zero in the same columns. This code works in conjunction with some custom Data validation applied to the same columns.
Code 2 basically just records a log of any changes made to the sheet on the sheet called 'Log'.
Both work on their own as expected. On the attached, I've commented out Code 2 to you can see Code 1 workinhg and if you comment out Code 2 and uncomment Code 1, you will see that working. However I need both Code 1 and Code 2 to work together, so if you uncomment both and then try and do something you will see the porblem.
So my question is simply, how can the two pieces of code co-exist and both work. I think the answer is simple, I'm just unsure how to do it because my VBA is limited.
Many thanks
Hi mikerickson,
I tried and failed, though I'm sure in practice it is probably very simple... I've added a sample to the previous post to demonstrate.
Hi,
When I uncomment the code that currently exists in the Worksheet_Selection Change procedure it resolves into three procedures, a Worksheet Change and two Worksheet Selection Change events which of course isn't permitted.
Hence it still isn't clear to me what you are trying to capture and the purpose of the data validation on F:I & L:M which refer to S1. In short I don't understand how you use this
Can we start with the attached which will log changes to column K. Tell me how you use any attempted change to F:I & L:M
Richard,
The code on your sheet is designed to do just that and only that, i.e. log any changes made to the sheet, though the original logs changes made to all cells. This has absolutely no bearing on F:I or L:M. These are two separate pieces of code to achieve two entirely different tasks.
All this code is doing is recording any changes to the sheet and recording those changes on the 'Log' tab. So that was all set up and working as expected.
I then had a requirement to add some additional functionality to the same sheet. Basically I needed a way to prevent users editing the figures in columns F:I and L:M (in the sample). I didn't want to protect the sheet because it would mean locking columns F:I and L:M resulting in the user being unable to insert/delete new rows, so the solution we came up with was for just columns F:I and L:M to add some Data Validation to those columns (leaving the sheet unprotected).
The way this works is as follows. This additional code (note is has been tweaked slightly since the original post) means that when a user clicks in a cell the value in that cell is shown in cell S1. Then using Custom Data Validation for coulmns G:I and L:M means that in conjunction with the code the user can only enter the existing value in any of those cells. If they insert a nre row then they can only enter zero in the cells in those columns.
Please Login or Register to view this content.Please Login or Register to view this content.
If the only reason is to prevent users changing stuff in those columns then it seems an unnecessarily complicated way.
Personally I'd protect the sheet and lock cells that may not be changed, then simply have a button which runs a macro that temporarily uprotects the sheet, adds a new row wherever it is needed (based on the active cell perhaps) and reprotects the sheet.
I didn't understand the comment "the user can only enter the existing value in any of those cells." If the user is clicking in a cell but you don't want anything different to that existing cell why he need to do anything?
And I still don't understand having two Selection Change events when the code is uncommented.
Locking the sheet has the potential to cause a number of other issues as well which is why I adopted the approach we did.
I think the problem has been solved though your help is very much appreciated.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks