Place cursor to the row which contains similar code while navigate to worksheet [SOLVED]

[inddon]

Hello There,

I have a workbook which contains 4 worksheets. These worksheets contains Tables. The common column across these worksheets is the Primary Key called 'Code'.

The requirement is as follows:
1. The current active worksheet is 'Sheet1'. The user cursor is on row whose 'Code' is XX-204.
2. When the user clicks on worksheet 'Sheet2', the cursor should be placed on row whose Code = XX-204.
3. The same goes for other sheets as well and vice versa.

I have attached the sample workbook for your reference.

Could you please advise on how this can be achieved?

Looking forward to hearing from you.

Thanks & regards
Don

 

[Deepak]

This might be possible but after doing so you will not able to copy/paste the data.

 

[inddon]

Hello Deepak,

Thank you for you reply. I did not quite get what you say.

Just a thought: Is it possible using doing the following:
1. Store the value of the current row's Code (in Sub Worksheet_SelectionChange).
2. When the worksheet is clicked, loop through clicked worksheet's table column name 'Code' and find the above stored value (from Step 1). When found, place the cursor to that row, if not give a message 'Not found' and then place the cursor to the 1st data row of the table in the clicked worksheet.

I don't know how to do this in VBA, if you could help would be great.

Thanks & regards
Don

 

[NARAYANK991]

Hi Don ,

See your file.

As Deepak has already mentioned , because of the macro running each time you activate and deactivate a sheet tab , your UNDO stack will be cleared , and you will not be able to undo your last action(s).

Narayan

 

[inddon]

Hi Don ,

See your file.

As Deepak has already mentioned , because of the macro running each time you activate and deactivate a sheet tab , your UNDO stack will be cleared , and you will not be able to undo your last action(s).

Narayan

Hello Narayan,

I understand now what Deepak was saying. Currently with the actual workbook with so many VBA code, the Undo function does not work in the Tables. So with your code example that is absolutely fine.

Thanks a lot for the code sample, it works as expected. I have learned yet another new VBA code from you today. Before applying I will debug and do my best to understand your code. If any questions I will let you know.

Thanks to Deepak too.

Narayan, May I ask you to please tag this post as [SOLVED] for me.


Regards
Don

 

[jeffreyweir]

Hi all. Note that the Undo Stack only gets cleared if the Macro changes something on the sheet. In this case, because nothing gets changed, the Undo stack does not get wiped.

 

[Deepak]

Hi all. Note that the Undo Stack only gets cleared if the Macro changes something on the sheet. In this case, because nothing gets changed, the Undo stack does not get wiped.

Ya!!
Because same is being controlled by workbook code event instead of worksheet event..

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

 

[inddon]

Hi all. Note that the Undo Stack only gets cleared if the Macro changes something on the sheet. In this case, because nothing gets changed, the Undo stack does not get wiped.

Hello Jeffrey,

I tried entering some values in the table (Sheet1), and then navigated to the Sheet2 (VBA code executed). Came back to the Sheet1 (VBA code executed). Did Ctrl-Z, the values entered were undone.

I understand from Deepak, code written on workbook level Undo is not wiped out, but on worksheet level. Learned another new thing on VBA, thanks.

I remember in one of my past post I had raised this issue. One of the excel Ninjas said the clearing of the Undo might be a bug.

Regards
Don

 

[inddon]

Hi Don ,

See your file.

As Deepak has already mentioned , because of the macro running each time you activate and deactivate a sheet tab , your UNDO stack will be cleared , and you will not be able to undo your last action(s).

Narayan

Hello Narayan,

Now the User has to place the cursor on the Table Column 'Code' and then the functionality works. Guess it is from the code : PrimaryKey = ActiveCell.Value

Could this code be made more generic:
The cursor can be on any Table Column and it keeps the Table Column 'Code' value in the memory for that row and the functionality works further?


Thanks and looking forward to hearing from you.

Regards
Don

 

[NARAYANK991]

Hi Don ,

See if this is OK.

Narayan

 

[inddon]

Hi Don ,

See if this is OK.

Narayan

Hello Narayan,

Thank you for the file.

I did the below test and found a small change needed. If you could advise and help:

(A)
Active sheet: Sheet1
Cursor is placed on empty cell Column 'Name' in row for Code XX-209

(B)
Navigate to Sheet2
Cursor goes to first table row for Code XX-201 and not on XX-209

This behavior happens only when the cursor is placed on empty cells, else it works fine.

Regards
Don

 

[NARAYANK991]

Hi Don ,

Since we are going by the same rule for all columns , this is the expected behaviour. If you want to change this , do you suggest that when ever the active cell is blank , any filled cell in the same row should be used , and when the sheet is changed , the cursor should be placed in the appropriate cell ?

Narayan

 

[inddon]

Hi Don ,

Since we are going by the same rule for all columns , this is the expected behaviour. If you want to change this , do you suggest that when ever the active cell is blank , any filled cell in the same row should be used , and when the sheet is changed , the cursor should be placed in the appropriate cell ?

Narayan

Hello Narayan,

Thank you for your reply.

The only common Table columns in all the Sheets' tables are:
'Sr.' and 'Code'. The number of columns in the all worksheet tables can vary.

I have attached the workbook 'Worksheet Lookup'. There is a new worksheet 'Explain' which gives a picture of how it should work. Would be nice if it can be done as seen in the picture.

My apologies if I did not explain it well before, that is why I have included the picture in the workbook.

Thanks for your time

Regards
Don

 

[NARAYANK991]

Hi Don ,

See if this is OK.

Narayan

 

[inddon]

Hi Don ,

See if this is OK.

Narayan

Hello Narayan,

Works wonderfully good

Thanks a lot for your time and the solution.

Regards
Don