Excel - setting a variable value

[simon551]

I have a page that is supposed to set some cells to the values other cells. I want to be able to control setting the values back to their original values though, and override/replace what those original values are if necessary. The only part that isn't working is the part about setting the new "original" value.


This is in a module:

Sub Button18_Click()
        
        'this should be setting the value of the variables to the current content of the cells, 
'but instead it is setting the value of the cells to the old value of the variable I don't understand why.
        
        OrgBaseRate = Range("BaseRate").Value
        OrgBillRate = Range("BillRate").Value
        
        Range("Update") = "<None>"   
        
End Sub

This is not:

Public OrgBaseRate As Variant
Public OrgBillRate As Variant


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Skip
' The combobox is named "Update".
    If Target.Name.Name = "Update" Then
        ' Save the orginal data
        If IsEmpty(OrgBaseRate) Then OrgBaseRate = Range("BaseRate").Value
        If IsEmpty(OrgBillRate) Then OrgBillRate = Range("BillRate").Value
    
                  ' Restore the original values
                Range("BaseRate") = OrgBaseRate
                Range("BillRate") = OrgBillRate
                ' The Update combobox has changed
        Select Case Target.Value
            Case "Pay"
                Range("BaseRate") = Range("BaseRateSource").Value
                Range("BillRate") = OrgBillRate
            Case "Bill"
                Range("BillRate") = Range("BillRateSource").Value
                Range("BaseRate") = OrgBaseRate
            Case Else
                ' Restore the original values
                Range("BaseRate") = OrgBaseRate
                Range("BillRate") = OrgBillRate
        End Select
               
    End If
        If Target.Name.Name = "Target_Margin" Then Range("Update") = "<None>"
        
           
Skip:

End Sub

 

[SkipVought]

Put a break in your code so it pauses as soon as you change a value on the sheet. Then STEP thru the code and observe what's happening using the Watch Window.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

You declared your variables as public in worksheet's module, so they become r/w properties of this worksheet. Consequently:
[tt]GetVariableValue =WorksheetCodeName.VariableName[/tt]
or
[tt]GetVariableName = Worksheets("WorksheetName").VariableName[/tt]
If variable and range are in the same worksheet, "Me" can be used as worksheet's reference.

combo