Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-16-2017, 07:30 PM
YW3 YW3 is offline Windows 8 Office 2003
Novice
 
Join Date: Jan 2017
Posts: 10
YW3 is on a distinguished road
Default Auto populate cell based on certain situations


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?
Reply With Quote
  #2  
Old 05-17-2017, 05:30 AM
NBVC's Avatar
NBVC NBVC is offline Windows 7 64bit Office 2007
Advanced Beginner
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 32
NBVC is on a distinguished road
Default

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.
Reply With Quote
  #3  
Old 05-17-2017, 06:08 PM
YW3 YW3 is offline Windows 8 Office 2003
Novice
 
Join Date: Jan 2017
Posts: 10
YW3 is on a distinguished road
Default

Thank you! It worked!
Reply With Quote
  #4  
Old 05-18-2017, 02:15 AM
YW3 YW3 is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2017
Posts: 10
YW3 is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 05-18-2017, 04:40 AM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 304
jeffreybrown is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 05-18-2017, 08:06 PM
YW3 YW3 is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2017
Posts: 10
YW3 is on a distinguished road
Default

"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

Last edited by YW3; 05-18-2017 at 10:40 PM.
Reply With Quote
  #7  
Old 05-18-2017, 08:18 PM
YW3 YW3 is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2017
Posts: 10
YW3 is on a distinguished road
Default

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.

Last edited by YW3; 05-18-2017 at 11:38 PM.
Reply With Quote
  #8  
Old 05-19-2017, 04:36 AM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 304
jeffreybrown is on a distinguished road
Default

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.
Reply With Quote
  #9  
Old 05-21-2017, 10:33 PM
YW3 YW3 is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2017
Posts: 10
YW3 is on a distinguished road
Default

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.
Attached Files
File Type: xlsm 35537Dummy.xlsm (20.3 KB, 3 views)

Last edited by YW3; 05-22-2017 at 08:25 PM.
Reply With Quote
  #10  
Old 05-22-2017, 12:09 AM
YW3 YW3 is offline Windows 10 Office 2016
Novice
 
Join Date: Jan 2017
Posts: 10
YW3 is on a distinguished road
Default

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?
Reply With Quote
  #11  
Old Yesterday, 02:02 AM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 304
jeffreybrown is on a distinguished road
Default

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
Reply With Quote
Reply

Tags
autopopulate
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 12:29 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft