Dynamically changing a user entry cell value

[Plasmech]

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.

 

[Peter_SSs]

Welcome to the MrExcel board!

I want the 100 to automatically change to 212.

How would you know that the user was not actually entering 100 degrees F in the first place?

Or if the user (with the drop-down set to degrees F) entered 212, is there any way to know if they have really entered 100C but knowing that conversion did it themselves so entered 212 or whether they have entered 212C and need it converted to F?

Depending what these temperatures represent, perhaps 34 might be a better example. If it was an air temperature measurement that could easily be F or C.

 

[Plasmech]

Hi Peter. Sorry for the delayed reply. I am new to the forum.

I should have been more detailed in my OP.

Yes, the user will manipulate the "degrees X" cell. When the units setting is changed, however, I want to execute a one-time conversion of said "degrees X" cell. The user can then change the value as many times as he wants. If the units are changed again, however, another one-time conversion will automatically happen.

Hope this makes sense.

 

[Peter_SSs]

Hope this makes sense

I'm afraid that it is not clear enough to me to make any specific suggestion(s).

 

[Plasmech]

I'm afraid that it is not clear enough to me to make any specific suggestion(s).

Perhaps an example will help:

Cell A1 is the numeric temperature value cell. The user can change this value.
Cell B1 shows what units A1 is in.

The user opens the sheet. Cell A1 is set to 75. Cell B1 shows "deg. F".

The user changes A1 to 212. He then decides that he'd rather work in Celsius. He changes the units setting via a drop-down menu from "deg. F" to "deg. C". As soon as "deg. C" is selected and entered, cell A1 automatically changes to 100, and cell B1 changes to "deg. C". He then leaves the units setting at "deg. C" and makes several more changes to A1 that are unaffected by the automatic unit-change conversion logic.

Does this example help?

 

[Peter_SSs]

Does this example help?

Yes, thanks.
You could try this Worksheet_Change event code, but note the warning below.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B1")) Is Nothing And Not IsEmpty(Range("B1").Value) Then
    Application.EnableEvents = False
      With Range("A1")
        If Right(Range("B1").Value, 1) = "C" Then
          .Value = (.Value - 32) * 5 / 9
        Else
          .Value = .Value * 9 / 5 + 32
        End If
      End With
    Application.EnableEvents = True
  End If
End Sub

Warning:
Example: A1 =100 and B1 = "deg. C"
User changes B1 to "deg. F" then the code will automatically change A1 to 212.
Suppose the user decides to change B1 back to deg. C but after clicking the drop-down arrow, changes their mind and again selects "deg. F" then A1 will change to 413.6
That is, the code will see the 212 that was in A1 as deg C and convert it to deg F.

If you think that could be a problem for you then further measures would be required to determine if cell B1 id actually changed when a value is entered into it. Post back if you need that.

 

[Plasmech]

Peter,

Thanks! You are extremely helpful. I'm sorry that it took me a bit to convey what I am trying to do.

I will need to add a check to the logic to ensure that only ONE automatic change can happen per unchanged temperature value.

In other words, I would not want cascading / stacking automatic changes if someone kept toggling the units back and forth without changing the value.

Is that possible to do?

Thanks again.

 

[Peter_SSs]

if someone kept toggling the units back and forth without changing the value.

If they are toggling the drop-down back and forward I don't think there is a problem as the conversion would keep reversing correctly. The problem comes if the choose the same drop-down value twice or more in a row.

I would suggest using a helper cell to stop the multiple conversions in the same direction. I have used Z1 but it could be any cell and that column could be hidden if you want.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B1")) Is Nothing And Not IsEmpty(Range("B1").Value) Then
    If Range("B1").Value <> Range("Z1").Value Then
    Application.EnableEvents = False
      With Range("A1")
        If Right(Range("B1").Value, 1) = "C" Then
          .Value = (.Value - 32) * 5 / 9
        Else
          .Value = .Value * 9 / 5 + 32
        End If
      End With
      Range("Z1").Value = Range("B1").Value
    Application.EnableEvents = True
    End If
  End If
End Sub

 

[Plasmech]

Thanks yet again.

I've never done a VBA macro before.

I copied/pasted your source code into a module in VB Editor in Excel in a workbook. I then went to View, Marcos and the "Macro name" list was empty.

1. Did I do something wrong?
2. How would I invoke the macro automatically when the sheet is loaded?
3. Sorry for so many questions.

 

[Peter_SSs]

1. Did I do something wrong?

Yes, you have the code in the wrong place. You did not accurately follow steps 1 & 2 from post #6.