Link to home
Start Free TrialLog in
Avatar of ger2222
ger2222

asked on

#Excel work sheet macros issues

Hi,
I'm new to VB and thwe macros that run behind worksheets, we have a new employee who needs to be added to a drop down list in an excel worksheet. Can some one point me in the right direction - idiots guide ?
Thanks,
Ger
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Hi,
It depends what kind of dropdown list it is. Does the dropdown only appear when you select a cell, or is it always visible? If the latter, do you know whether it came from the Control Toolbox or the Forms toolbar?
Regards,
Rory
Avatar of ger2222
ger2222

ASKER

The dropdown only appears when i select the cell.
Hope this helps
In that case, there is probably no code involved. If you select the cell, choose Data-Validation from the menu, you will probably see that the Allow box is set to List and the Source box is set to a typed in list (unlikely), a range reference or a named range. For the first one, you could just type the new name in; for the range reference, you can just extend this by one row, then type the new name into that row on the worksheet; for a defined name, you will need to locate the data that the name refers to (you can do this via Insert-Name-Define form the menu, select the name in the list and then check the 'refers to' range. Again, add the new data in the next row, then update the name as necessary.
Regards,
Rory
PS If the name has been defined dynamically (using an OFFSET construction) you may just be able to add the new name in the next row and it will be picked up automatically.
Avatar of ger2222

ASKER

Hi,
The validation option is greyed out, i dont have the password to unprotect the sheet, how can i get around this? I'm new to thje job so dont have the password.
You can't change the validation if the sheet is protected so you have two options:
1. Find someone who does have the password! :)
2. Break the password. The code to do so is fairly basic or you can use a commercial tool. (I'm never entirely happy publishing code to do this but you can google it easily enough if necessary)
Regards,
Rory
Avatar of ger2222

ASKER

ok, i got the worksheet unprotected and can see the formula but colum U is hidden , how do I unhide just column U
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ger2222

ASKER

got it, thanks for your help and patience, thats my something new for today!!!  :)))
Glad to help! Thanks for the grade.
Rory