Dynamically changing a user entry cell value

Plasmech

New Member
Joined
Oct 26, 2021
Messages
44
Office Version
  1. 365
Platform
  1. Windows
Hello,

Say I have a cell where the user enters a temperature value that is used in a formula elsewhere in the sheet. They enter "100". The units, which are set via a user-configurable drop-down box, are currently set to degrees C.

Now let's say the user changes the units setting to degrees F. I want the 100 to automatically change to 212. I do not want it to simply become 100 (F).

How would I go about this? I somehow need to exert control over a non-formula cell

Thanks for any replies.
 
I went back to post #6 and read your instruction (as I should have in the first place!). The code is now working, but we are having the stacking conversion issue (yes, I used your 2nd source code, not the first one).

If I enter 100C and then select F units, is converts it to 212F (awesome!). But if I then select C units, it converts it to 414C, not 100C.

One thing different on my sheet is that I'm using "English" and "Metric" as my units names, and altered the source as follows:

If Right(Range("C1").Value, 1) = "Metric" Then
.Value = (.Value - 32) * 5 / 9
Else
.Value = .Value * 9 / 5 + 32
End If

In Z1, I see a mirror of the current units setting.
 
Upvote 0

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
If Right(Range("C1").Value, 1) = "Metric" Then
That change it incorrect.

It appears that you have also changed the Data Validation cell from B1 to C1.
Assuming that the temperature value cell has changed from A1 to B1, then this.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("C1")) Is Nothing And Not IsEmpty(Range("C1").Value) Then
    If Range("C1").Value <> Range("Z1").Value Then
    Application.EnableEvents = False
      With Range("B1")
        If Range("C1").Value = "Metric" Then
          .Value = (.Value - 32) * 5 / 9
        Else
          .Value = .Value * 9 / 5 + 32
        End If
      End With
      Range("Z1").Value = Range("C1").Value
    Application.EnableEvents = True
    End If
  End If
End Sub
 
Upvote 0
It's working!

This is awesome.

I've been wanting to figure this out for *years*. Glad I found this site.

Thank you so much, Peter.
 
Upvote 0
FWIW, I moved the Z1 "parking spot" cell closer to home and changed the text color to white (light gray at first to monitor it).
 
Upvote 0
I tried moving the parking spot to another worksheet called "lookup data" like this:

If Range("C4").Value <> Range("'lookup data'!E7").Value Then

but it threw a syntax error.

I should be able to call other sheets, right?

Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top