Password required to select value
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. |
Password required to select value
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. |
All times are GMT +1. The time now is 07:01 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com