VBA worksheet change and data update

Hello Experts,

I'm trying to build a report that I would like the end user to click on a cell on a certain worksheet, when the user clicks on the cell it then takes them to a different worksheet, however I also need it to update a cell on the new worksheet with information from the line that is click on the original worksheet.

Hoping someone can assist :-)

Thanks in advance
Chris_SizerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Yes, I'm sure we can help, but you are going to need to be a little more specific with terms like "certain", "different", & "a cell", or else you are going to get equally vague responses.

Please relay the name of the worksheets, the range of the rows (or the cells) that you wish to click on the first worksheet, & what is updated & where, in the other worksheet.

Thanks.

BFN,

fp.
0
Chris_SizerAuthor Commented:
Hello,

Thanks for your response! I have multiple worksheets that i need to work this on but if i can get one sorted i should be able to replicate from there.

Worksheets:

Channel - need to be able to click on cell 'C8' and have the value from 'B8' on this worksheet move across to the next worksheet which is
Region - and put the 'B8' cell value into cell 'B4' on this worksheet.

Does this assist?

Thanks in advance ;-)
0
[ fanpages ]IT Services ConsultantCommented:
That's fine, thank you.

The following code is taken from the Worksheet code module of the [Channel] worksheet within the attached workbook...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Target.Address = [C8].Address Then
     Worksheets("Region").[B4] = Worksheets("Channel").[B8]
  End If ' If Target.Address = [C8].Address Then
  
End Sub

Open in new window



If this approach is going to be used in multiple worksheets though, you may be better utilising the Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) event subroutine within the "ThisWorkbook" code module, & checking with "Sh" (worksheet) is active as the "Target" (cell) is selected.
Q-28264487.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Chris_SizerAuthor Commented:
Thank you!

This is almost there, the only thing I need now is to have it that when you click on Channel 'C8' cell that it also then moves you to the Region worksheet automatically - is this also possible?
0
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Sorry, I completely missed that within your question ("...then takes them to a different worksheet").

To add this functionality, please revise the code with an 'activation' of the required worksheet, as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Target.Address = [C8].Address Then
     Worksheets("Region").[B4] = Worksheets("Channel").[B8]
     Worksheets("Region").Activate
  End If ' If Target.Address = [C8].Address Then
  
End Sub

Open in new window


I have attached a new workbook with this additional line included within the Visual Basic for Applications code.
Q-28264487b.xls
0
Chris_SizerAuthor Commented:
Thank you :)
0
[ fanpages ]IT Services ConsultantCommented:
No problem at all.

Apologies again for missing that initially.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.