Change Value based on another cell (vba)

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Change Value based on another cell (vba)

  1. #1
    New Member
    Join Date
    Jun 2009
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Change Value based on another cell (vba)

     
    Hi guys,
    I'm come up to abit of a problem at work, on an excel form that we use I need it to automatically enter a value in a cell depending on if the value in another cell is "0"

    basically
    if C25 equals "0" then C26,C27 and C28 need to display "N/A"

    the value in C25 is selected from a list

    it has to be done in VBA and happen on its own if C25 = "0"
    C26,C27 and C28 also need to be able to be edited after

    Thanks
    AMR

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Change Value based on another cell (vba)

    Try this: right click the sheet tab, select View Code and paste in

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(False, False) = "C25" Then
        Application.EnableEvents = False
        If Target.Value = 0 Then
            Range("C26:C28").Value = [#N/A]
        Else
            Range("C26:C28").ClearContents
        End If
        Application.EnableEvents = True
    End If
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Oct 2004
    Posts
    602
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Change Value based on another cell (vba)

      
    If your form is in the excel sheet (using items from the form toolbar) VoGs code will not be called, in that case just change his code slightly (like below)
    Code:
    Private Sub SameAsVoGs()
    If Range("C25") = 0 Then
      Range("C26:C28").Value = [#N/A]
    Else
      Range("C26:C28").ClearContents
    End If
    End Sub
    Then right click your list, and choose AssignMacro and choose this macro (You can rename it obviously)

    Cheers
    GB

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com