Hi Graeme,

Is the dropdown box a combobox or data validation?

For data validation try this, although if it's a combobox it might be better
to load it every time the workbook is opened with the options being dependant
on who is logged onto the computer. That way if they disable macros they
won't be able to select the c/d without invoking the password.

Public myval 'value selected
Public hasRun As Boolean 'to prevent the inputbox recurrng

Private Sub Worksheet_Change(ByVal Target As Range)
If hasRun = False Then
Select Case Range("B4").Text
Case "C", "D"
myval = Range("B4") 'remember what was chosen
hasRun = True
Range("B4") = "" 'remove the contents of B4
If Application.InputBox("enter password") = "password" Then
Range("B4") = myval 'restore the chosen value
Else
Range("B4") = "" 'leave blank
End If
hasRun = False
End Select
End If
End Sub

"Graeme" wrote:

In cell b4, I have a drop-down box with 5 salary bands: A, B, C, D and E. I
also have a lookup table with associates a salary with each of these bands,
and this value is place in cell b5. I would like anyone to be able to select
A, B or C and see these salaries, but only people with a password to be able
to select D or E, and therefore see these salaries. I was hoping there was an
efficient way of doing this using Worksheet_Change(ByVal Target As Range) and
an Inputbox. Thank you.

Libby,

Thank you. I was using a drop-down box, and your code worked perfectly.

Graeme

"Libby" wrote:

Hi Graeme,

Is the dropdown box a combobox or data validation?

For data validation try this, although if it's a combobox it might be better
to load it every time the workbook is opened with the options being dependant
on who is logged onto the computer. That way if they disable macros they
won't be able to select the c/d without invoking the password.

Public myval 'value selected
Public hasRun As Boolean 'to prevent the inputbox recurrng

Private Sub Worksheet_Change(ByVal Target As Range)
If hasRun = False Then
Select Case Range("B4").Text
Case "C", "D"
myval = Range("B4") 'remember what was chosen
hasRun = True
Range("B4") = "" 'remove the contents of B4
If Application.InputBox("enter password") = "password" Then
Range("B4") = myval 'restore the chosen value
Else
Range("B4") = "" 'leave blank
End If
hasRun = False
End Select
End If
End Sub

"Graeme" wrote:

In cell b4, I have a drop-down box with 5 salary bands: A, B, C, D and E. I
also have a lookup table with associates a salary with each of these bands,
and this value is place in cell b5. I would like anyone to be able to select
A, B or C and see these salaries, but only people with a password to be able
to select D or E, and therefore see these salaries. I was hoping there was an
efficient way of doing this using Worksheet_Change(ByVal Target As Range) and
an Inputbox. Thank you.