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

[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
---

[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.

Please Login or Register  to view this content.

---

[ResulG]

Hey Guys,

Check this!

Thanks to you guys I have managed to create another event within the same event trigger.

ResulG

[Bryan Hessey]

Hey Guys,

Check this!

Thanks to you guys I have managed to create another event within the same event trigger.

ResulG

Hi,

I see only one Event trigger, with two condition tests, but it would have been more efficient to use the ElseIf rather than use a second If statement for the test.

But, good to see it works for you.

note, the Range of a trigger can be as wide or narrow as you need, A1:Z99 is fine, but of course there's always the processing overhead.
---

[ResulG]

You can tell I'm learning. However I will look into that and change it to else if statement.

By the way how ould i remove decimal places in a concatenate?
I will create a new thread for this i think!

Thanks again Bryan and Carim

ResulG

[Bryan Hessey]

You can tell I'm learning. However I will look into that and change it to else if statement.

By the way how ould i remove decimal places in a concatenate?
I will create a new thread for this i think!

Thanks again Bryan and Carim

ResulG

Hi,

The 'If' was in my old post, to remove the decimal in Concat, (a vague request) something like

=A1&" together with "&Text(Int(B1*100),"##0")

etc

Like that ?

---