Auto populate cell based on certain situations

YW3 · #1 05-16-2017, 07:30 PM

A1 is a drop down box containing either 'Yes' or 'No'.

In A2,
If A1 is yes, the user would manually key in a number. This number is a variable.
However, if A1 is no, I'd like A2 to auto-populate '0'.

Do note that A1 and A2 are merged cells.

Is this possible? How can I do this? If not possible, anyone have a suggestion for something similar?

NBVC · #2 05-17-2017, 05:30 AM

Hi,

You will need to include an event macro to accomplish that.

Try right-clicking on the tab of your sheet and select "View Code"

Paste this code in the editor.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
    If Range("A1").Value = "No" Then
        Range("A2").Value = 0
    Else
        Range("A2").Value = ""
    End If
End If

End Sub

Now go back to the sheet and test.

YW3 · #3 05-17-2017, 06:08 PM

Thank you! It worked!

YW3 · #4 05-18-2017, 02:15 AM

Is it possible to fix the 0 so after selecting "No" in A1, the user would not be able to edit A2 to be anything else than 0?

Same thing applies that if user select "Yes" in A1, then they'll be able to edit A2.

jeffreybrown · #5 05-18-2017, 04:40 AM

You could add a couple of lines to the code, but you also have to lock the spreadsheet and unlock A1 so the user can select between Yes and No.

The code will take care of locking and unlocking A2 based on the choice in A1, but not locking the spreadsheet.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
        If Range("A1").Value = "No" Then
            Range("A2").Value = 0
            Range("A2").Locked = True
        Else
            Range("A2").Value = ""
            Range("A2").Locked = False
        End If
    End If

End Sub

YW3 · #6 05-18-2017, 08:06 PM

"Run-time error '1004':

Unable to set the Locked property of the Range class

Continue | End | Debug | Help"

^ Happens regardless if I select "Yes" or "No".
Still able to edit A2 after selecting "No" and locking the sheet.

Both A1 and A2 are unlocked cells.

After I select Debug, the arrow points to:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
        If Range("A1").Value = "No" Then
            Range("A2").Value = 0
            Range("A2").Locked = True
        Else
            Range("A2").Value = ""
            Range("A2").Locked = False
        End If
    End If

End Sub

YW3 · #7 05-18-2017, 08:18 PM

Oh! I'm so sorry, I forgot to mention that I'm not applying it to just one cell. A2:F2, in which A2 and B2 are merged, C2 is an un-merged cell, D2 and E2 are merged, and F2 is another un-merged cell. So it's actually 4 cells that needs to auto-populate '0'.

I've been editing the code to add in :F2. Is that the error?

I'm so sorry I missed this out.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
        If Range("A1").Value = "No" Then
            Range("A2:F2").Value = 0
            Range("A2:F2").Locked = True
        Else
            Range("A2:F2").Value = ""
            Range("A2:F2").Locked = False
        End If
    End If

End Sub

Edit: I tried it again with just one cell only (test both merged and un-merged), it still didn't work. I don't know what the problem is then.

jeffreybrown · #8 05-19-2017, 04:36 AM

First problem is those merged cells. You need to get rid of them if possible. Merged cells maybe are not 100% bad, but when you start using VBA code, merged cells cause extreme havoc.

Please see if you can fix the merged cells, but also, please attach a sample workbook with what you have and what you expect.

YW3 · #9 05-21-2017, 10:33 PM

In the Dummy workbook attached, A1 in this post would be B16 in excel, and A2, B19.

Sheet1: I've un-merged the cells in A2 but the code still doesn't work, the same error pops up.
Sheet2: I would think A1 is the problem then, and tried un-merging it. Still doesn't work. Same pop up error.

Due to other cells, I would prefer if A1 remains merged. And that if you got any alternatives suggestions, it does not involve changing column width.

YW3 · #10 05-22-2017, 12:09 AM

I'm not familiar with macro so I really don't know but the error "Unable to set the Locked property of the Range class" means it's A2 that's the problem? Range("A2")? Is it because I tried to apply to multiple cells? A2:F2/B19:E19?

jeffreybrown · #11 05-26-2017, 02:02 AM

Sorry for the delay, been traveling for business...

Try...

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveSheet.Unprotect Password:="aaa"
    Dim rng As Range
    Set rng = Target.Parent.Range("B16")
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    With Range("B19:E19")
        Select Case Target.Value
        Case "No"
            .Value = 0
            .Locked = True
        Case "Yes"
            .Value = ""
            .Locked = False
        End Select
    End With
    ActiveSheet.Protect Password:="aaa"
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel to auto populate dates based on start and end	patidallas22	Excel Programming	9	04-21-2014 03:14 PM
trying to use vlookup to calculate several different situations in one cell	heastlund	Excel	3	11-21-2013 12:38 PM
auto cell populate 2	Stevejd58	Excel	3	08-20-2013 04:23 PM
auto cell populate	Stevejd58	Excel	14	05-13-2013 02:22 AM
Auto-populate an MS Word table cell with text from a diff cell?	dreamrthts	Word Tables	0	03-20-2009 01:49 PM