Making cells non selectable on condition 2

[slames]

Hi, I'm looking for some help with VBA for excel. I have a drop down menu in a cell, with choices Paperback or Hardback. If paperback is selected in this cell I need a subsequent cell to grey out - I can do this, but I also need to make it unselectable as the cell needs to remain empty, if it is filled in mistakingly then the calculation will fail. Can anyone help me?.

Thanks in advance

Steph

 

[tbl]

I'm not quite sure that I understand what you mean with grey-out and not selectable. I can tell you how to ensure that the cell always remains clear if paperback is selected.
Which type of drop-box are you using, Button or Command Button ?

Richard

 

[slames]

I am just using the data validation list as the drop down box, I really don't know a lot about using vba in Excel. By unselectable I mean that when the user clicks on the cell I don't want them to be able to write in/change what is already in there. Hope that makes it a bit clearer. Would you be ablr to tell me how to make sure the cell remains clear?

Thanks

Steph

 

[tbl]

If you just want to protect the cell then lock it and use sheet protection. If it is sometimes permissable to write in the cell, then I can give you a VBA solution, but I need to know which type of box you are using.Are you using any VBA at the moment ?

Richard

 

[xlbo]

in your worksheet CHANGE event

If target.address <> range(&quot;B3&quot.address then exit sub
If range(&quot;A3&quot.value <> &quot;Paperback&quot; then exit sub
range(&quot;B3&quot.value = &quot;&quot;



Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[slames]

In answer to TBL:

No I am not using any VBA at the moment, I can not lock the sheet as I need the user to enter text in to other cells on the sheet. I don't believe it is possible just to lock one cell - or is it?

Thank You

Steph

XLBO: any chance you could explain your code please, sorry I really don't have a lot of experience with excel.

Thanks

 

[xlbo]

In excel, press ALT+F11 - this will take you to the Visual Basic Editor. You should be able to see a &quot;Projects&quot; window. In the projects window, you should be able to see your workbook with a list of the worksheets. Double click on the worksheet which this code needs to apply to. This should create a sheet module. Choose &quot;Worksheet&quot; from the left dropdown and an empty Selection_Change sub will be generated. Choose &quot;Change&quot; from the right dropdown and an empty change event sub will be created. Delete the selection_change sub and paste the 3 lines I posted earlier into the change sub

Amend A3 and B3 to be the cells where the dropdown list is and the cell which needs to be empty respectively and you should be away

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[tbl]

Step 1 Add the Visual Basic buttons to the toolbar

Step 2 Press the VisualBasic Editor Button to open VBA

Step 3 Use View to select Project Explorer if not open

Step 4 Click twice on the sheet you are using and enter xlbo's code into the righthand pane. Change the cell values to suit.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If target.address <> range(&quot;B3&quot.address then exit sub
If range(&quot;A3&quot.value <> &quot;Paperback&quot; then exit sub
range(&quot;B3&quot.value = &quot;&quot;


End Sub

 

[slames]

Thanks TBL and XLBO, I now have this working, thanks for youe patience! Can you recomend a general excel VBA web page for code samples/tips?, apart from this of course!

 

[tbl]

You have everything you need right here !

 

[xlbo]

http://www.cpearson.com/excel/topic.htm

http://www.ozgrid.com/default.htm

http://j-walk.com/ss/

http://www.fontstuff.com/

http://www.bmsltd.co.uk/Excel/Default.htm

http://www.decisionmodels.com/optspeedb.htm

are all in my favourites

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[slames]

Thanks!