Link to convert pounds to kgs?

[leopardhawk]

Hello forum friends, I am wondering if it might be at all possible to have a worksheet with some text that was hyperlinked in such a way that clicking it would convert numerous cells (~45) containing numbers representing pounds to kilograms? Some cells are where the user actually enters data (in pounds) and there are other cells containing formulas that return results in pounds (if that makes a difference). Conversely, there would also be a way to convert them back to pounds.

Just wondering if there are any ideas out there about how this might be accomplished.

Thanks!

 

[MARK858]

They'll be doing the equivalent, just in a different language like JavaScript.
Edit:
Or because they are fixed values they might even have hard coded the values in the code

 

[Fluff]

Another option, without VBA

Leopardhawk.xlsm
	A	B	C
1			Kgs
2	1	1
3	2	2
4	3	3
5	4	4
6	5	5
7	6	6
8	7	7
9	8	8
10	9	9
11	10	10
12	11	11
13	12	12
14	13	13
15	14	14
16	15	15
17	16	16
18	17	17
19	18	18
20	19	19
21	20	20
22	21	21
23	22	22
24	23	23
25	24	24
26	25	25
Sheet1

Cell Formulas
Range	Formula
B2:B26	B2	=IF(C$1="Kgs",A2,CONVERT(A2*1000,"g","lbm"))

Cells with Data Validation
Cell	Allow	Criteria
C1	List	Kgs, lbm

You could then hide Col A

 

[Dave Patton]

You could use Conditional Formatting to alternate the view that you require Pounds or Kilograms.
The post shows extra information because I showed both arithmetic and Convert.
The Custom Formatting with the Conditional formatting could be ";;;"

T202006a.xlsm
	B	C	D	E	F	G
1	Unit required		a
2		Pounds	Kilograms	Pounds
3		2	0.91	2
4		4	1.81	4
5		6	2.72	6
6		20	9.07	20
7		1000	453.59	1000
8
9
10	Units	Type	Convert		Convert
11	2	lbm	0.907	kg	0.907	kg
12	4	lbm	1.814	kg	1.814	kg
13	20	lbm	9.072	kg	9.072	kg
14	1000	lbm	453.590	kg	453.592	kg
15	0.907	kg	2.000	lbm	2.000	lbm
16	1.814	kg	4.000	lbm	4.000	lbm
17	9.072	kg	20.000	lbm	20.000	lbm
18	453.590	kg	999.985	lbm	999.995	lbm
8c

Cell Formulas
Range	Formula
D3:D7	D3	=CONVERT(C3,"lbm","g")/1000
E3:E7	E3	=CONVERT(D3*1000,"g","lbm")
D11:D18	D11	=IF(C11="lbm",B11*0.45359,IF(C11="kg",B11*2.2046,""))
E11:E18	E11	=IF(C11="lbm","kg",IF(C11="kg","lbm",""))
F11:F18	F11	=IF(C11="lbm",CONVERT(B11,"lbm","g")/1000,IF(C11="kg",CONVERT(B11*1000,"g","lbm"),""))
G11:G18	G11	=IF(C11="lbm","kg",IF(C11="kg","lbm",""))
B15:B16	B15	=D11

 

[JoeMo]

Very lightly tested, but using Mark's code with a worksheet_FollowHyperlink event rather than a worksheet_change event appears to work with a single hyperlink that's linked to itself.

In this example, I used the ribbon menu>Insert>Links>Hyperlink to link cell A1 to itself, entering "Switch to lbs" in the "Text to display" box and A1 in the cell reference box. Then right-click the worksheet tab, choose View Code and paste in the code below. Clicking the link triggers the code which applies the appropriate multiplier to the values in A2 and down, then changes the "Text to display" to invoke the alternate units multiplier when clicked again. Needs some polishing, like rounding of converted results so if you start with 1 lb > convert to 0.45359237 kg and back again you get 1 rather than 0.9999999... lbs.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
If Target.Range.Count = 1 And Target.Range.Address = "$A$1" Then
        Application.EnableEvents = False

        If Target.Range.Value = "Switch to Kg" Then
            With Range("A2", Cells(Rows.Count, "A").End(xlUp))
                .Value = Application.Evaluate(.Address & "*" & 0.45359237)
            End With
            Target.Range.Value = "Switch to lbs"
            Application.EnableEvents = True
            Exit Sub
        End If
   
        If Target.Range.Value = "Switch to lbs" Then
            With Range("A2", Cells(Rows.Count, "A").End(xlUp))
                .Value = Application.Evaluate(.Address & "*" & 2.20462262)
            End With
            Target.Range.Value = "Switch to Kg"
            Application.EnableEvents = True
            Exit Sub
        End If
        Application.EnableEvents = True
    End If
End Sub