PDA

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