Solved

#Excel work sheet macros issues

Posted on 2007-03-26
9
270 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

757 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

17 Experts available now in Live!

Get 1:1 Help Now