Results 1 to 3 of 3

Change Value based on another cell (vba)

This is a discussion on Change Value based on another cell (vba) within the Excel Questions forums, part of the Question Forums category; Hi guys, I'm come up to abit of a problem at work, on an excel form that we use I ...

  1. #1
    AMR
    AMR is offline
    New Member
    Join Date
    Jun 2009
    Posts
    7

    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
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,514

    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

    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

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