Hi! I have a worksheet and I would like to use a "part-time" drop-down list.
What I mean is that cell B1 will look at cell A1. If cell A1 shows "N/A",
then B1 will automatically display "N/A" as well. If A1 shows anything other
than "N/A", i.e. some useful information has been added, then B1 becomes a
defined drop-down list. (I have already made and named the validation list).

I know it might sound lazy, but my company wants this worksheet to be as
automatic as possible, so instead of typing "N/A" into both A1 and B1, the
user will just need to type if in once (in A1).

Is there a way of doing this?

I had hoped there might be a formula along the lines of
=IF(A1="N/A","N/A", {{validation as explained}})

Maybe I'm asking too much, but if anybody knows how to do it I'd be very
grateful!
Cheers, Neil

I think the only way you can do this is with an event macro, since once
you choose something from the validation menu, any formula will be
overwritten.

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Range("A1").Value = "N/A" Then
Application.EnableEvents = False
Range("B1").Value = "N/A"
Application.EnableEvents = True
End If
End If
End Sub


Be sure to put N/A in your validation list - the macro bypasses it, but
otherwise the user will wonder why s/he can't directly enter it.


In article ,
Neil Goldwasser <Neil wrote:

Hi! I have a worksheet and I would like to use a "part-time" drop-down list.
What I mean is that cell B1 will look at cell A1. If cell A1 shows "N/A",
then B1 will automatically display "N/A" as well. If A1 shows anything other
than "N/A", i.e. some useful information has been added, then B1 becomes a
defined drop-down list. (I have already made and named the validation list).

I know it might sound lazy, but my company wants this worksheet to be as
automatic as possible, so instead of typing "N/A" into both A1 and B1, the
user will just need to type if in once (in A1).

Is there a way of doing this?

I had hoped there might be a formula along the lines of
=IF(A1="N/A","N/A", {{validation as explained}})

Maybe I'm asking too much, but if anybody knows how to do it I'd be very
grateful!
Cheers, Neil

Thanks for that - that was great! Just one more question if I may though (I
apologise if the answer is a simple one, I am very much a macro novice!)

How could I adapt this code so that it works not just for cell A1, but for a
range of cells. What would I need to change for this function to work for all
of column A, or for cells A1 to A100, for example.

Many thanks, Neil


"JE McGimpsey" wrote:

I think the only way you can do this is with an event macro, since once
you choose something from the validation menu, any formula will be
overwritten.

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Range("A1").Value = "N/A" Then
Application.EnableEvents = False
Range("B1").Value = "N/A"
Application.EnableEvents = True
End If
End If
End Sub


Be sure to put N/A in your validation list - the macro bypasses it, but
otherwise the user will wonder why s/he can't directly enter it.


In article ,
Neil Goldwasser <Neil wrote:

Hi! I have a worksheet and I would like to use a "part-time" drop-down list.
What I mean is that cell B1 will look at cell A1. If cell A1 shows "N/A",
then B1 will automatically display "N/A" as well. If A1 shows anything other
than "N/A", i.e. some useful information has been added, then B1 becomes a
defined drop-down list. (I have already made and named the validation list).

I know it might sound lazy, but my company wants this worksheet to be as
automatic as possible, so instead of typing "N/A" into both A1 and B1, the
user will just need to type if in once (in A1).

Is there a way of doing this?

I had hoped there might be a formula along the lines of
=IF(A1="N/A","N/A", {{validation as explained}})

Maybe I'm asking too much, but if anybody knows how to do it I'd be very
grateful!
Cheers, Neil

There are many ways, here's one:

For column A:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Columns(1))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "N/A" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

For A1:A100:

Change

Columns(1)

to

Range("A1:A100")



In article ,
Neil Goldwasser <Neil wrote:

Thanks for that - that was great! Just one more question if I may though (I
apologise if the answer is a simple one, I am very much a macro novice!)

How could I adapt this code so that it works not just for cell A1, but for a
range of cells. What would I need to change for this function to work for all
of column A, or for cells A1 to A100, for example.

Many thanks, Neil


"JE McGimpsey" wrote:

I think the only way you can do this is with an event macro, since once
you choose something from the validation menu, any formula will be
overwritten.

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Range("A1").Value = "N/A" Then
Application.EnableEvents = False
Range("B1").Value = "N/A"
Application.EnableEvents = True
End If
End If
End Sub

Perfect! Thanks very much for that! I'm really starting to come around to
macros now.

Am I right in thinking that I might cause problems for this code if I needed
to insert new columns between A & B, so that the cell that needs to
automatically turn to N/A depending on the outcome of A1 is no longer next to
it?

Is there an easy way of adapting the code so that, for example, cells
A1:A100 are the first cells, chosen from a drop-down list, and cells E1:E100
are the ones that need to automatically change to "N/A" if the corresponding
cell in the A-range is "N/A".

Sorry to keep asking so many questions, but since this is my first dabble
with Visual Basic I'm fascinated by it all!

Many thanks again, Neil



"JE McGimpsey" wrote:

There are many ways, here's one:

For column A:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Columns(1))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "N/A" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

For A1:A100:

Change

Columns(1)

to

Range("A1:A100")



In article ,
Neil Goldwasser <Neil wrote:

Thanks for that - that was great! Just one more question if I may though (I
apologise if the answer is a simple one, I am very much a macro novice!)

How could I adapt this code so that it works not just for cell A1, but for a
range of cells. What would I need to change for this function to work for all
of column A, or for cells A1 to A100, for example.

Many thanks, Neil


"JE McGimpsey" wrote:

I think the only way you can do this is with an event macro, since once
you choose something from the validation menu, any formula will be
overwritten.

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Range("A1").Value = "N/A" Then
Application.EnableEvents = False
Range("B1").Value = "N/A"
Application.EnableEvents = True
End If
End If
End Sub

Sorry, one more idea to add to my last one. Let's say my situation is as
described in my last post (which I have not yet managed to write code for,
but if somebody could advise me might be possible):

Cells A1:A100 are the original drop-down list
Cells E1:E100 are the cells that will automatically change to "N/A" if the
corresponding A cell is "N/A"

If I entered "N/A" from the drop-down list into A1, E1 would automatically
turn to "N/A" as well. But I am then able to click on E1 and select a
different value from the drop-down list, overwriting the "N/A". Is there any
way of disallowing this overwrite, so that if A1 is "N/A", E1 stays as "N/A",
even if you try to change it?

It would be even better if a message box could pop up as well and say
something along the lines of "This cell must remain as N/A, because cell the
corresponding A cell is N/A".

If I could add these two new changes (this and the one from my last post)
I'd be over the moon!
Thanks again for the help, it is much appreciated.
Neil



"Neil Goldwasser" wrote:

Perfect! Thanks very much for that! I'm really starting to come around to
macros now.

Am I right in thinking that I might cause problems for this code if I needed
to insert new columns between A & B, so that the cell that needs to
automatically turn to N/A depending on the outcome of A1 is no longer next to
it?

Is there an easy way of adapting the code so that, for example, cells
A1:A100 are the first cells, chosen from a drop-down list, and cells E1:E100
are the ones that need to automatically change to "N/A" if the corresponding
cell in the A-range is "N/A".

Sorry to keep asking so many questions, but since this is my first dabble
with Visual Basic I'm fascinated by it all!

Many thanks again, Neil



"JE McGimpsey" wrote:

There are many ways, here's one:

For column A:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Columns(1))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "N/A" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

For A1:A100:

Change

Columns(1)

to

Range("A1:A100")



In article ,
Neil Goldwasser <Neil wrote:

Thanks for that - that was great! Just one more question if I may though (I
apologise if the answer is a simple one, I am very much a macro novice!)

How could I adapt this code so that it works not just for cell A1, but for a
range of cells. What would I need to change for this function to work for all
of column A, or for cells A1 to A100, for example.

Many thanks, Neil


"JE McGimpsey" wrote:

I think the only way you can do this is with an event macro, since once
you choose something from the validation menu, any formula will be
overwritten.

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Range("A1").Value = "N/A" Then
Application.EnableEvents = False
Range("B1").Value = "N/A"
Application.EnableEvents = True
End If
End If
End Sub

I've made an example here on creating dependent listing. This may solve
your problem without using macros ... :
http://www.pvv.org/~nsaa/excel.html#24

Thanks for the advice, it is much appreciated. My apologies for not being
able to send my thanks earlier - I have been away for a while on a
much-needed break.

I am still curious though as to whether there is a way of writing a macro as
described in my last post. It is the idea of the message box that most
appeals, so that the user is informed of the reason why they cannot change
the "N/A" displayed in cell E1. Some of the staff at my organisation are not
particularly computer literate and I know that I will get several phonecalls
asking why they can only choose "N/A" from the drop-down list if I used the
'dependent listing' method as you have suggested. If I could insert the
message box function, that would answer their queries for me, and it would
avoid me having to explain it several times over.

Again, thanks to everybody who has helped, Neil



" wrote:

I've made an example here on creating dependent listing. This may solve
your problem without using macros ... :
http://www.pvv.org/~nsaa/excel.html#24