View Full Version : Solved: Drop Down Box -
gimli
04-29-2010, 05:16 AM
Hi all,
Need some advise...
I have a drop down box which which populates another cell with data based on a formula relating to the drop down selection.
If A then use formula "blah blah"
If B then use formula " bee bop"
etc etc
I would like to add an option called lets say custom to the drop down which will clear the formula and allow someone to put a custom value in. The cell is locked so it would have to be unlocked also.
is something like this possible? Open for alternative suggestions.
Hope im explaining that right.
lucas
04-29-2010, 07:30 AM
You want us to build this to test it for you?
This is why your thread gets looked at many times before any one jumps in.........
gimli
04-29-2010, 07:34 AM
Lucas,
Yes that would be awesome :beerchug:
If so I can look at it and build on it.
thanks much
lucas
04-29-2010, 07:38 AM
This is a learning site. We aren't here to deliver full solutions.
You will have to do some of the work.
As far as I can see, there is nothing to look at.
gimli
04-29-2010, 07:56 AM
Ok then thats what I expected you to say.
Like I said before im a noob. Dont expect full solution...dont know where to start with that one.
lucas
04-29-2010, 08:18 AM
What would a formula look like? psuedo code wise.
for instance would it be an if this then that kind of thing or sums or what exactly?
gimli
04-29-2010, 08:32 AM
Ill post the code for what I have...that will help.
Sorry for the unclear posts
austenr
04-29-2010, 09:52 AM
I can answer this but wont based on Steves comment above.
gimli
04-29-2010, 11:48 AM
Hey all,
Here is some code. Its basic...I stripped out alot of stuff. I put some notes in the file.
When the selection custom is made via drop down I would like some cells to unprotect so data can be entered. And when the other options are selected the cells will be protected again and the data will populate via formula.
thanks much
austenr
04-29-2010, 05:48 PM
gimli, see if you can figure this out.
Private Sub Worksheet_Change(ByVal Target As Range)
If [B3] = "Promotion" Then
ActiveSheet.Unprotect ("PASSWORD")
[S3].Locked = True
ActiveSheet.Protect ("PASSWORD")
'Remove locked property if B3's value is anything else or is deleted.
Else
ActiveSheet.Unprotect ("PASSWORD")
[S3].Locked = False
'Optional, reprotect sheet
ActiveSheet.Protect ("PASSWORD")
End If
End Sub
This works but it is not tailored to your needs.
gimli
04-30-2010, 05:49 AM
Snap!
Ok..thanks for the suggestions/help. Think I got what I need.. this works perfectly! Thanks for putting up with me here HA! :thumb
Work sheet
Option Explicit
Private Sub TOLCHOICE_Change()
ActiveSheet.Unprotect "snap"
On Error GoTo Exits
Application.EnableEvents = False
Call Sandi
Call Mel
ActiveSheet.Protect "snap"
Exits:
Application.EnableEvents = True
End Sub
Module
Option Explicit
Sub Mel()
If Range("F12") = 0 Then
Range("E12") = " "
ElseIf Range("S12") = "CUSTOM" Then
Range("E12") = " "
[E12].Locked = False
[E12].Interior.ColorIndex = 36
ElseIf Range("S12") = "A" Then
Range("E12") = Range("K14")
[E12].Locked = True
[E12].Interior.ColorIndex = 39
ElseIf Range("S12") = "B" Then
Range("E12") = Range("K15")
[E12].Locked = True
[E12].Interior.ColorIndex = 39
End If
End Sub
Sub Sandi()
If Range("F13") = 0 Then
Range("E12") = " "
ElseIf Range("S12") = "CUSTOM" Then
Range("E13") = " "
[E13].Locked = False
[E13].Interior.ColorIndex = 36
ElseIf Range("S12") = "A" Then
Range("E13") = Range("N16")
[E13].Locked = True
[E13].Interior.ColorIndex = 39
ElseIf Range("S12") = "B" Then
Range("E13") = Range("N17")
[E13].Locked = True
[E13].Interior.ColorIndex = 39
End If
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.