Solved

#Excel work sheet macros issues

Posted on 2007-03-26
9
277 Views
Last Modified: 2010-04-16
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
0
Comment
Question by:ger2222
  • 5
  • 4
9 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18792459
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
0
 

Author Comment

by:ger2222
ID: 18792481
The dropdown only appears when i select the cell.
Hope this helps
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18792520
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.
0
 

Author Comment

by:ger2222
ID: 18792546
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18792583
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
0
 

Author Comment

by:ger2222
ID: 18792633
ok, i got the worksheet unprotected and can see the formula but colum U is hidden , how do I unhide just column U
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 250 total points
ID: 18792662
Select columns T and V and choose Format-Column-Unhide from the menu.
0
 

Author Comment

by:ger2222
ID: 18792675
got it, thanks for your help and patience, thats my something new for today!!!  :)))
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18792719
Glad to help! Thanks for the grade.
Rory
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now