Hide or display column depending on cell content?

[Niko]

Hi,

Can anyone help me with the following?

I want to design a questionnaire for 10 - 20 people in a spreadsheet.
But I don't want those people to have a look at the others's answers,
because I don't want them to influence each other.
I am thinking of the following way to do that:
In column A, I put the questions. They of course are to be seen by
everybody. Besides, in the top part of column A, I have an empty cell (say,
A1) in which every participant can put a password: the password varies for
everyone.
The answers are to be filled in in the columns, B, C, etc. In cell B1, C1,
etc. the passwords are stored: they are different for each column.

Now, what I want is that all columns (starting from B) are hidden. If
someone enters in A1 the password of, say, column D, automatically column D
is displayed. And as soon as you change or delete this password in A1,
column D hides again.

So, I need something like:
if D1=A1 then column D is displayed else column D is hidden.
And of course this should be true for about 20 columns, starting from B.
All this without having to press a button, etc., because I want it to be as
userfriendly as possible.

Is this possible?

Niko

 

[Don Guillett]

I suggest a select case statement something like. Could be automatic with
worksheet_change event.

Sub hidecolifnopassword()
Columns("b:g").Hidden = True
Select Case UCase(Range("a1"))
Case Is = "JOE": x = "b"
Case Is = "BILL": x = "c"
'etc
Case Else
MsgBox "No": Exit Sub
End Select
Columns(x).Hidden = False

End Sub

 

[merjet]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim rng As Range

If Target.Address = "$A$1" Then
For Each c In Range(Cells(1, 2), Cells(1, 20))
If Target = c Then
c.EntireColumn.Hidden = False
Else
c.EntireColumn.Hidden = True
End If
Next c
End If

End Sub

Hth,
Merjet

 

[Niko]

Thanks, I can see the logic of this.
However - I apologize - I am an absolute dummy when it comes to using this
code.
I opened the VBA window with Alt-F11 (that much I know at least) and copied
the procedure to the module window.
But I don't know how to go on from there. How do I get the procedure to work
automatically, each time someone changes the contents of the cell A1?
Should I put some reference to the procedure in a cell?
I tried to run the procedure as a macro, but then I got the answer 'Argument
not optional'.
So next I retried, using 'A1' as an argument, but that delivered the
reaction 'Reference is not valid'.
So that is obviously not the way to do it. Besides, the procedure should not
be executed after running a macro but automatically, after each change of
A1.
Can you tell me how to do that?

Niko

 

[merjet]

Put it in the code module for the sheet.

Merjet

 

[Niko]

Thanks. I had never heared of a worksheet_change event. It turns out to be
the solution, so you were absolutely right.

Niko

 

[Niko]

Yes! It works. Thank you!

Niko

 

[Don Guillett]

Glad to help