#1
|
|||
|
|||
Hide a combo box (form control), based on a cell value - Excel 2010
Hi guys
I'm TOTALLY new to this. I am using Excel 2010 I have a worksheet called "Price Calculator", In it are 4 form control combo boxes: 1) "Drop Down 3", linked to cell $A$11 2) "Drop Down 11" 3) "Drop Down 12" 4) "Drop Down 13" (I've got these combo box names from the section to the left of the formula bar when the respective combo box is right clicked) What I'm trying to achieve is: Whenever the cell link value to "Drop Down 3" <2 (i.e. when $A$11 <2) , I want the other 3 combo boxes to be automatically hidden And whenever $A$11 >= 2, I want them to reappear Could someone please help me with the VBA code? And where do I put this code exactly? (in case I've been doing that bit wrong) I have tried everything I can find on the internet, but nothing seems to trigger a change event Would really appreciate your help |
#2
|
|||
|
|||
The change of value in the linked cell does not fire the Worksheet_Change event.
You could use the Worksheet_SelectionChange event but the 3 drop downs would not hide and unhide when the drop down 3 changes A11 until the cursor gets moved into another cell. This could leave things in the wrong state for considerable time. I'd use the Worksheet_Calculate event. This requires making another cell somewhere on the sheet =A11 to make sure something gets calculated the instant the value in A11 changes. The Calculate event fires every time something, anything, on the sheet gets calculated so there is the potential that having this event do things could affect Excels performance in which case you could have the =A11 cell on a separate sheet with nothing else and move the code over to that sheet. Right click on the sheet tab your drop downs are on, select view code then paste this into the sheet module that appears. Code:
Private Sub Worksheet_Calculate() Dim ws As Worksheet Set ws = Sheets("Price Calculator") If Range("A11") < 2 Then ws.Shapes("Drop Down 11").Visible = False ws.Shapes("Drop Down 12").Visible = False ws.Shapes("Drop Down 13").Visible = False Else ws.Shapes("Drop Down 11").Visible = True ws.Shapes("Drop Down 12").Visible = True ws.Shapes("Drop Down 13").Visible = True End If End Sub |
#3
|
|||
|
|||
Hide a combo box (form control), based on a cell value - Excel 2010
Thank you NoSparks!! Very helpful
Correct me if I'm wrong, my understanding of what you've said is: 1) Start a new worksheet in the same workbook, say its called "Backend" 2) Put a formula in a cell, say C1, and that formula should be = 'Price Calculator'!$A$11 3) Change your code where it says: If Range("A11") < 2 Then To If Range("C1") < 2 Then 4) Paste this code onto the "Beckend" worksheet, and NOT the "Price Calculator" worksheet If I have understood you correctly, what would the code call the combo boxes?? Would they not have to be referenced to the "Price Calculator" worksheet?? PS I have tried your code exactly as it is straight into the worksheet coding, and it works a treat but would still like the above guidance if poss!!!! |
#4
|
|||
|
|||
Yes, that's what I said. The only change would be If Range("C1") < 2 Then
The drop downs stay qualified as they are. Must admit though, I haven't tested it this way. Let me know if that works for you. |
#5
|
|||
|
|||
NoSparks,
It actually works without creating the second worksheet, and I have tested it perhaps not rigorously, but extensively. I have actually adapted your code (i have never coded before) to include other form control objects, as follows: Private Sub Worksheet_Calculate() Dim ws As Worksheet Set ws = Sheets("Price Calculator") If Range("A11") < 2 Then ws.Shapes("Drop Down 11").Visible = False ws.Shapes("Drop Down 12").Visible = False ws.Shapes("Drop Down 13").Visible = False ws.Shapes("Spinner 29").Visible = False Else ws.Shapes("Drop Down 11").Visible = True ws.Shapes("Drop Down 12").Visible = True ws.Shapes("Drop Down 13").Visible = True ws.Shapes("Spinner 29").Visible = True End If If Range("A11") < 3 Then ws.Shapes("Drop Down 17").Visible = False ws.Shapes("Drop Down 18").Visible = False ws.Shapes("Drop Down 19").Visible = False ws.Shapes("Spinner 31").Visible = False Else ws.Shapes("Drop Down 17").Visible = True ws.Shapes("Drop Down 18").Visible = True ws.Shapes("Drop Down 19").Visible = True ws.Shapes("Spinner 31").Visible = True End If If Range("C9") = 0 Then ws.Shapes("Button 51").Visible = False ws.Shapes("Button 52").Visible = False ws.Shapes("Drop Down 42").Visible = False ws.Shapes("Spinner 41").Visible = False Else ws.Shapes("Button 51").Visible = True ws.Shapes("Button 52").Visible = True ws.Shapes("Drop Down 42").Visible = True ws.Shapes("Spinner 41").Visible = True End If End Sub This works a treat!! What everyone else on internet forums got 'wrong' is they had it down as a 'change' event, not a 'calculation' event. You are officially a scholar, gentleman, and a genius!! With great thanks!! |
#6
|
|||
|
|||
Quote:
Glad I could help. Please mark this thread as solved and put links in your cross posts to all your other cross posts, as stipulated in all the forums rules, so others searching for a solution to a similar challenge can learn from our experience. |
#7
|
|||
|
|||
You said:
"Please mark this thread as solved and put links in your cross posts to all your other cross posts, as stipulated in all the forums rules, so others searching for a solution to a similar challenge can learn from our experience." I have no idea how to do that lol, but I'm on the case... Thanks again |
#8
|
|||
|
|||
I have now marked it 'Solved', but have no idea how to put links on my 'cross posts' to all other 'cross posts'.... I'm not sure I know what 'cross posts' are...
|
#10
|
|||
|
|||
Quote:
Here is the link to the cross post: http://www.excelforum.com/excel-prog...el-2010-a.html |
Tags |
combo box, combobox, vba macro |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to Hide/Un-hide a worksheet based on cell on another sheet. | easton11 | Excel Programming | 1 | 06-02-2015 12:07 PM |
Word 2010 Content Control help - Combo Boxes vs Drop Down List | proghy | Word | 1 | 09-16-2014 02:01 PM |
Allow Multiple Content Control same cell of form table | DeborahBartlett | Word | 1 | 01-04-2014 11:06 AM |
Hide Rows and Update Chart based on cell value | ubns | Excel Programming | 5 | 05-07-2012 05:44 AM |
Hide a combo box | DrewB | Word | 3 | 06-08-2009 11:29 PM |