Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Revert to a default value (Excel 2000 SP3)

    The scenario is:

    When a worksheet is opened there is a default value in a particular cell. If users wants to change the default value they are able to do so and therefore the cell cannot be protected.

    If users think that the value entered in the cell is not correct and delete the newly entered value the cell I want the cell to show the default value again. A formula cannot be used for the default value because in entering the users entry in the cell will overwrite the formula.

    Can this requirement be done? I would appreciate assistance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Revert to a default value (Excel 2000 SP3)

    One option is to create a macro that restores the default value. Users can run this macro when they need it. Say that the cell is B3 and the default value is 37.

    Sub RestoreDefault
    Range("B3") = 37
    End Sub

    Another option is to use three cells.
    Say B1 contains the default. This cell is locked.
    The user can enter a value in B2.
    Cell B3 is locked again, and this contains the formula <code>=IF(B2="",B1,B2)</code>. If the user enters a value in B2, this is returned, otherwise the default value from B1 is returned. You can use B3 in further calculations.

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Revert to a default value (Excel 2000 SP3)

    Thank you and HansV for your responses to my enquiry.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Revert to a default value (Excel 2000 SP3)

    If you want the reversion to the default value to be automatic, then you could put the following code in the worksheet change event routine:

    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C2")) Is Nothing Then
    If Range("C2").Value = "" Then
    Application.EnableEvents = False
    Range("C2").Value = 1234
    Application.EnableEvents = True
    End If
    End If
    End Sub
    </code>


    Edited by LegareColeman to eliminate some overhead caused by unnessary event processing
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Revert to a default value (Excel 2000 SP3)

    I have a similar question. In the sample attachment, after a user enters a name, I would like the Response 1 and Response 2 to default to "N". A user can then change this response if they wish to "Y". However, if possible, I only want the default value to be entered if the name field goes from "" to a value. In other words, if the name has already been entered, and a user changes it (spelling issues, etc.), I don't want the values to go back to "N" if the user already changed them.
    Attached Files Attached Files

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Revert to a default value (Excel 2000 SP3)

    You can use something like this in the module behind the worksheet (right-click the tab, choose View Code and paste this in):

    <pre>Option Explicit
    Const mcstrDefault As String = "N"
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCell As Range, rngCheck As Range
    If Intersect(Target, Me.Columns(1)) Is Nothing Then Exit Sub
    On Error GoTo err_handler
    Application.EnableEvents = False
    Set rngCheck = Intersect(Target, Me.Columns(1))
    For Each rngCell In rngCheck
    If Len(rngCell.Offset(0, 1)) = 0 Then rngCell.Offset(0, 1) = mcstrDefault
    If Len(rngCell.Offset(0, 2)) = 0 Then rngCell.Offset(0, 2) = mcstrDefault
    Next rngCell

    leave:
    Application.EnableEvents = True
    Exit Sub

    err_handler:
    MsgBox "Error: " & Err.Description
    Resume leave
    End Sub
    </pre>


    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Revert to a default value (Excel 2000 SP3)

    Rory, Thanks!! The code worked great!

    Now that I have implemented that code with my exisiting code, Columns C, D, J, K, and L are very slow at updating (where the ChangeCase functions are called) while column G, J, K, and L (where your code has been applied for default values) is very quick. Any thoughts to make my code more effieicent?


    Option Explicit
    Const mcstrDefaultType As String = "New Policy"
    Const mcstrDefault As String = "N"

    Private Sub Worksheet_Change(ByVal Target As Range)
    'Change case in Column C or D to Proper
    If Not Intersect(Target, Range("C")) Is Nothing Then
    Application.EnableEvents = False
    ActiveSheet.Unprotect
    Call ChangeCases1
    ActiveSheet.Protect
    Application.EnableEvents = True
    End If

    'Change case in Column J, K, or L to Upper
    If Not Intersect(Target, Range("J:L")) Is Nothing Then
    Application.EnableEvents = False
    ActiveSheet.Unprotect
    Call ChangeCases2
    ActiveSheet.Protect
    Application.EnableEvents = True
    End If

    'Enter Default Values for Data Entry
    Dim rngCell As Range, rngCheck As Range
    If Intersect(Target, Me.Columns(6)) Is Nothing Then Exit Sub
    On Error GoTo err_handler
    Application.EnableEvents = False
    Set rngCheck = Intersect(Target, Me.Columns(6))
    For Each rngCell In rngCheck
    If Len(rngCell.Offset(0, 1)) = 0 Then rngCell.Offset(0, 1) = mcstrDefaultType
    If Len(rngCell.Offset(0, 4)) = 0 Then rngCell.Offset(0, 4) = mcstrDefault
    If Len(rngCell.Offset(0, 5)) = 0 Then rngCell.Offset(0, 5) = mcstrDefault
    If Len(rngCell.Offset(0, 6)) = 0 Then rngCell.Offset(0, 6) = mcstrDefault
    Next rngCell

    leave:
    Application.EnableEvents = True
    Exit Sub

    err_handler:
    MsgBox "Error: " & Err.Description
    Resume leave

    End Sub

    Sub ChangeCases1()
    'Change names to Proper Case
    Dim i As Long
    For i = 2 To Range("D72").End(xlUp).Row
    Range("C" & i) = Application.WorksheetFunction.Proper(Range("C" & i))
    Range("D" & i) = Application.WorksheetFunction.Proper(Range("D" & i))
    Next i
    End Sub

    Sub ChangeCases2()
    'Change Y or N responses to capital letters
    Dim i As Long
    For i = 2 To Range("L72").End(xlUp).Row
    Range("J" & i) = UCase(Range("J" & i))
    Range("K" & i) = UCase(Range("K" & i))
    Range("L" & i) = UCase(Range("L" & i))
    Next i
    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Revert to a default value (Excel 2000 SP3)

    You should only apply proper case / upper case to the cells that have actually changed, instead of to all cells. See attached version.
    Attached Files Attached Files

  9. #9
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Revert to a default value (Excel 2000 SP3)

    Much better and much quicker!! Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •