Hyperlink to set cell value

[ResulG]

Hi All,

I was wondering if i could set the value of a cell simply by creating hyperlinks and clicking on them.
For example I want to be able to change to value of A1 simply by clicking on any one of the following countries. So if I click on Belgium, the A1 will change to Belgium, if i click on Germany, A1 will change to Germany.

Belgium
Germany
Finnland
France
Ireland
Italy
Luxemburg
Netherland
Austria
Portugal
Spain

I'm working on it my self and still looking for a way to overcome this.

Is this even possible?

I'm working to create a currency conversion in excel to improve my knowledge of the program.
I will share it with the forum once its complete.

Thanks

Resul

[Bryan Hessey]

Hi All,

I was wondering if i could set the value of a cell simply by creating hyperlinks and clicking on them.
For example I want to be able to change to value of A1 simply by clicking on any one of the following countries. So if I click on Belgium, the A1 will change to Belgium, if i click on Germany, A1 will change to Germany.

Belgium
Germany
Finnland
France
Ireland
Italy
Luxemburg
Netherland
Austria
Portugal
Spain

I'm working on it my self and still looking for a way to overcome this.

Is this even possible?

I'm working to create a currency conversion in excel to improve my knowledge of the program.
I will share it with the forum once its complete.

Thanks

Resul

Hi,

The attached works the other way, but should be easy to switch the A: to the B:

Unfortunately it requires a double-click to activate, but should suit your purpose.

hth
---

[ResulG]

This is a start for me.

Thanks Bryan,

But are there any other ways of achieving the same?
This method edits the text when you double click which could result in the text being changed.
Also a hyperlink would be more appropriate.

Thanks Again

ResulG

[Carim]

Hi,

Bryan's suggestion, to my knowledge, is the best solution ...

What makes you think an hyperlink would be better, what would be the added benefit...?

HTH
Carim

[ResulG]

Hi,

I'll settle with Bryan's answer. It fulfills ma need.
However I was just hunting for any other methods of employing the same process.

Also, is it possible to use the same method again?
Obviously i have to use the countries twice, as to converting from and to.
I tried to use the same code but it didnt work? I changed it around and played with it but couldnt get it to work!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim iColumn As Integer, iRow As Integer
With ActiveSheet
iColumn = Target.Column
If iColumn = 1 Then
iRow = Target.Row
Cells(2, 2) = Cells(iRow, 1)
End If
End With
End Sub

Private Sub Worksheet_BeforeDoubleClick1(ByVal Target As Excel.Range, Cancel As Boolean)
Dim tColumn As Integer, tRow As Integer
With ActiveSheet
tColumn = Target.Column
If tColumn = 3 Then
tRow = Target.Row
Cells(4, 2) = Cells(tRow, 3)
End If
End With
End Sub

Any help will be appriciated

Thanks

ResulG

[Bryan Hessey]

Hi,

I'll settle with Bryan's answer. It fulfills ma need.
However I was just hunting for any other methods of employing the same process.

Also, is it possible to use the same method again?
Obviously i have to use the countries twice, as to converting from and to.
I tried to use the same code but it didnt work? I changed it around and played with it but couldnt get it to work!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim iColumn As Integer, iRow As Integer
With ActiveSheet
iColumn = Target.Column
If iColumn = 1 Then
iRow = Target.Row
Cells(2, 2) = Cells(iRow, 1)
End If
End With
End Sub

Private Sub Worksheet_BeforeDoubleClick1(ByVal Target As Excel.Range, Cancel As Boolean)
Dim tColumn As Integer, tRow As Integer
With ActiveSheet
tColumn = Target.Column
If tColumn = 3 Then
tRow = Target.Row
Cells(4, 2) = Cells(tRow, 3)
End If
End With
End Sub

Any help will be appriciated

Thanks

ResulG

HI,

This is an Event trigger, you can have only one.

You can test for column 1 AND then test column 3 and perform different actions.

If iColumn = 1 Then
iRow = Target.Row
Cells(2, 2) = Cells(iRow, 1)
ElseIf iColumn = 3 Then
iRow = Target.Row
Cells(4, 2) = Cells(iRow, 3)
End If

---

[Bryan Hessey]

This is a start for me.

Thanks Bryan,

But are there any other ways of achieving the same?
This method edits the text when you double click which could result in the text being changed.
Also a hyperlink would be more appropriate.

Thanks Again

ResulG

Hi,

If you are concerned about text changing, then select all cells and remove the protection, select those cells that need protection and set the 'Protect', then protect the worksheet, no password.

This would preserve the required data.

hth
---