Excel VBA - Push link and select item in dropdown list

[behedwin]

Hi

I have a book with two sheets
sheet 1 and sheet2

In sheet2 i have a data validation dropdown list created based on data from sheet1

When user are in sheet1
i want the user to be able to click on a value (that is part of the dropdown list on sheet2) and then go to sheet2 and have the dropdown list load the clicked value.

So a quick way to go from sheet1 and based on what value the user clicks on, have that value selected in a the dropdown list in sheet2.

It is easy to just create a link from sheet1 to sheet2
but the problem i have is to make the action that when user click take that cells value and select that value in the dropdownlist.

 

[jim may]

Does this help?

https://www.contextures.com/xlDataVal02.html

 

[behedwin]

To be honest i dont know...
The examples i´ve found show two dropdown lists... but only have one.

I want that if the user click on the link "Banana" in sheet1 the dropdownlist in sheet2 should set itself to Banana
And if the user click on Apple in sheet1 the dropdown list in sheet2 should be set to Apple.

In sheet1 i do not have any dropdown lists.
In sheet1 i have a list of values that are configured as links to sheet2.
So i want to add a function that when a user clicks on a link, also select the links value in the dropdownlist.

 

[MickG]

Try Validation list in both sheets "A1":-

'Code in sheet2 module.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address(0, 0) = "A1" Then
    Sheets("Sheet1").Range("A1").Value = Target
End If
Application.EnableEvents = True
End Sub

'Code in sheet1 module.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address(0, 0) = "A1" Then
    Sheets("Sheet2").Range("A1").Value = Target
End If
Application.EnableEvents = True
End Sub

 

[behedwin]

Try Validation list in both sheets "A1":-

'Code in sheet2 module.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address(0, 0) = "A1" Then
    Sheets("Sheet1").Range("A1").Value = Target
End If
Application.EnableEvents = True
End Sub

'Code in sheet1 module.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address(0, 0) = "A1" Then
    Sheets("Sheet2").Range("A1").Value = Target
End If
Application.EnableEvents = True
End Sub

I tried this, cant get it to work.
Sheet1´s name is = skola översikt
Sheet2´s name is = enskild skola

In sheet1 i have a list of names
A4:A14
Each name are a hyperlink to sheet2 (enskild skola).

I have pasted this code in VBA editor in ÖVERSIKT SKOLA (SHEET1)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address(0, 0) = "A1" Then
    Sheets("Enskild skola").Range("A1").Value = Target
End If
Application.EnableEvents = True
End Sub

I have posted this code in VBA editor in ENSKILD SKOLA (SHEET2)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address(0, 0) = "A1" Then
    Sheets("Skola översikt").Range("A1").Value = Target
End If
Application.EnableEvents = True
End Sub

When i click on a link in SHEET1 (Översikt Skola) in range A4:A14
Nothing happens to the dropdown list in SHEET2 (Enskild Skola) A1 cell.

 

[MickG]

My code assumes you have 2 validation lists as specified in your first thread (both with the same data) one in each sheet in cell "A1"
If you have those lists and you have pasted the code in the Worksheet modules (Right click sheet tab , Module for that sheet appears, paste code) then the codes should work.