Change Value based on another cell (vba)

AMR

New Member
Joined
Jun 18, 2009
Messages
7
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top