Solved: Drop Down Box -

[gimli]

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]

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]

Lucas,

Yes that would be awesome

If so I can look at it and build on it.

thanks much

[lucas]

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]

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]

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]

Ill post the code for what I have...that will help.

Sorry for the unclear posts

[austenr]

I can answer this but wont based on Steves comment above.

[gimli]

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]

gimli, see if you can figure this out.

[VBA]
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
[/VBA]

This works but it is not tailored to your needs.

[gimli]

Snap!

Ok..thanks for the suggestions/help. Think I got what I need.. this works perfectly! Thanks for putting up with me here HA!

Work sheet

[VBA]
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
[/VBA]

Module

[VBA]
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
[/VBA]