The Excel Questions Thread

Originally Posted by Cior

Click to expand...

Perhaps someone can explain where our problem is arising in the below scenario:

We have a spreadsheet that has three tabs. The first two tabs are protected and require a password to edit outside the unprotected areas (essentially a form). The third sheet is not protected but contains a macro that prompts for a password prior to viewing the sheet.

On sheet number one we have approximately 100 rows and 30 columns. The second (protected) and third (password macro'ed) sheet contain the same setup and pull the information typed into the first sheet.

Here is the dilemma:

We created a button (developer tab) to hide and unhide various rows depending on how much information the user has. The goal was that when the user clicked the button on sheet #1 it would hide those correlating rows on sheet #1 as well as sheet #2 and #3. To do this required another macro that would first unprotect (via the password) hide/unhide the rows/columns and then reprotect (with password). This worked correctly for the rows portion of our challenge. However for sheet one and two on the columns button attempt it worked, but it is not working on sheet #3 the macro password protected one when attempting to hide/unhide a series of connected rows (say M:O) after unprotecting.

And actually it works correctly with the rows but when attempting to do the same thing with columns the columns on the third sheet are not hidden/unhidden.

Any thoughts or ideas why this is not working correctly. Or why is would work with connecting rows but now connecting columns? Do the columns need to be the same on all 3 sheets?

The first thing I'd check is whether or not there are any merged cells across the columns that you want to hide & unhide. If so, un-merge them and run the macro. Otherwise, I'd have to see the spreadsheet to help any further.

Below is the macro that works only on sheets "active" and "Project Budget", but does not hide the columns on sheet "Cost Analysis"

Sub Add_Or_Remove_Classification_9()
ActiveSheet.Unprotect ("XXX")
'
' Add_Or_Remove_Personnel Macro
'

'
Columns("AA:AC").Hidden = Not Columns("AA:AC").Hidden
Range("AB6").Select
Sheets("Project Budget").Unprotect ("XXX")
Sheets("Project Budget").Columns("AA:AC").Hidden = Columns("AA:AC").Hidden
Sheets("Project Budget").Protect ("XXX")
Sheets("Cost Analysis").Unprotect ("XXX")
Sheets("Cost Analysis").Columns("AA:AC").Hidden = Columns("AA:AC").Hidden
ActiveSheet.Protect ("XXX")
End Sub


Below is a similar macro that works correctly on all three sheets. It hides/unhides rows and not columns.


Sub Add_Or_Remove_Task_4()
ActiveSheet.Unprotect ("XXX")
'
' Add_Or_Remove_Task_4 Macro
'

'
Rows("43:54").Hidden = Not Rows("43:54").Hidden
Range("C45").Select
Sheets("Project Budget").Unprotect ("XXX")
Sheets("Project Budget").Rows("43:54").Hidden = Rows("43:54").Hidden
Sheets("Project Budget").Protect ("XXX")
Sheets("Cost Analysis").Unprotect ("XXX")
Sheets("Cost Analysis").Rows("43:54").Hidden = Rows("43:54").Hidden
ActiveSheet.Protect ("XXX")
End Sub


Below is the macro used to password protect just the sheet "Cost Analysis" above. I do not believe it is the problem as stated above the rows macro works fine on all three sheets but the columns macro does not. As such I don't think this macro is the problem but wanted to post it for thoroughness.

Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
Me.Protect Password:="XXX"
Me.Columns.Hidden = True


strPassword = InputBox("Password Protected Content", "XXX")


If strPassword = "" Then
Me.Previous.Select
Exit Sub
ElseIf strPassword <> "XXX" Then
MsgBox "Incorrect Password", "XXX"
Me.Previous.Select
Exit Sub
Else
Me.Unprotect Password:="XXX"
Me.Columns.Hidden = False
End If


On Error GoTo 0
End Sub



Any thoughts on why the columns macro (1st one) is not correctly hiding the corresponding columns on sheet "Cost Analysis"?

^ Did you verify that there are no merged cells that cross columns AA, AB or AC?

There are merged cells between AA and AC but none of them are between the hidden sections. ie none of the merged cells overlap the edges of Z and AD.

Originally Posted by Cior

Click to expand...

There are merged cells between AA and AC but none of them are between the hidden sections. ie none of the merged cells overlap the edges of Z and AD.

The code seems pretty straight forward and simple and doesn't look like anything in it would be throwing off the hiding of columns. Make a test copy of your workbook and un-merge all of the cells that are merged across columns AA:AC and run the code again. This may not work, but I just wanted to completely rule it out. BTW, when you say there are merged cells between AA and AC, but none between the hidden sections, do you mean that there are no merged cells within the hidden rows?

I don't know if this is possible, but I'll give it a try. I have a workbook with 13 sheets - one sheet has FY10 budget data, and the following 12 are monthly expenditure tracking. My hope is to get the expenditure tracking sheet to create a running total for line items in a single cell. Basically, I want cell xx in September to link to the total of July and August, and I want it to sum these with September's new entry. So if we spend $1,000 in July and August, and $500 in September, I want to be able to key 500 in the September xx cell and have Excel automatically combine it with the prior $1,000 to give me $1,500. The problem I'm having is that I have no idea how to make this happen in single cell. I can do it in multiple cells if need be, but it's a lot of data to roll over each month, so I'm trying to limit the number of cells I add.

Any ideas?

Originally Posted by MrG

Click to expand...

I don't know if this is possible, but I'll give it a try. I have a workbook with 13 sheets - one sheet has FY10 budget data, and the following 12 are monthly expenditure tracking. My hope is to get the expenditure tracking sheet to create a running total for line items in a single cell. Basically, I want cell xx in September to link to the total of July and August, and I want it to sum these with September's new entry. So if we spend $1,000 in July and August, and $500 in September, I want to be able to key 500 in the September xx cell and have Excel automatically combine it with the prior $1,000 to give me $1,500. The problem I'm having is that I have no idea how to make this happen in single cell. I can do it in multiple cells if need be, but it's a lot of data to roll over each month, so I'm trying to limit the number of cells I add.

Any ideas?

It's possible to show a running tally, but it may not look exactly how you want it to look. It'll really depend on how you're setting up your data on each worksheet. Are you going to be keeping a running tally down each worksheet for each month's expenditures showing the expenditure descriptions and amounts and dates? At least in my mind, the setup would look like this:

Code:

well no luck so far in getting excel sheets automatically sent to powerpoint but i'm not giving up!

on a related question, i have a macro that takes the file, breaks the links and saves it to a specific file name...works great. The problem is when someone else uses it. The linked files are located on a shared server, which in my case is mapped to my w: drive. Others map it to whatever letter they want.

So my macro says to break the link w:\\foldername\\filename.xls but the macro has a problem if say they have the file mapped to their t: drive.

I noticed that when I add a hyperlink to the file in the macro, it does reference it in a generic way (servername\\foldername\\filename.xls) versus w:\\foldername\\filename.xls

I wanted to see if i could put in the link based on the servername versus the drive letter...anyone know how to do that?

thanks!

Originally Posted by Neo1824

Click to expand...

well no luck so far in getting excel sheets automatically sent to powerpoint but i'm not giving up!

on a related question, i have a macro that takes the file, breaks the links and saves it to a specific file name...works great. The problem is when someone else uses it. The linked files are located on a shared server, which in my case is mapped to my w: drive. Others map it to whatever letter they want.

So my macro says to break the link w:\\foldername\\filename.xls but the macro has a problem if say they have the file mapped to their t: drive.

I noticed that when I add a hyperlink to the file in the macro, it does reference it in a generic way (servername\\foldername\\filename.xls) versus w:\\foldername\\filename.xls

I wanted to see if i could put in the link based on the servername versus the drive letter...anyone know how to do that?

thanks!

The following link isn't exactly what you want, but it gets you the server name, etc, if the user inputs the letter of their drive. I've never used it and it's a little too advanced for what I've been able to teach myself, but check it out anyway.

http://support.microsoft.com/kb/160529

You can also use the =INFO("Directory") formula to return the letter of the drive on which the open file is saved. It'll return it in the format "A:\\", however you have to actually type the formula into the workbook. To automate that you can write some simple code, like mine below, to fill in the formula, grab the name of the drive and delete the formula:

Code:

Let us know what solution you use.

Originally Posted by dsgNYC

Click to expand...

The following link isn't exactly what you want, but it gets you the server name, etc, if the user inputs the letter of their drive. I've never used it and it's a little too advanced for what I've been able to teach myself, but check it out anyway.

http://support.microsoft.com/kb/160529

You can also use the =INFO("Directory") formula to return the letter of the drive on which the open file is saved. It'll return it in the format "A:\\", however you have to actually type the formula into the workbook. To automate that you can write some simple code, like mine below, to fill in the formula, grab the name of the drive and delete the formula:

Code:

With Worksheets("Sheet1")
.Range("IV65536").Formula = "=INFO(""Directory"")"
sharedPath = .Range("IV65536")
.Range("IV65536").ClearContents
End With
​

Let us know what solution you use.
Thanks. I'm trying to figure out how to make this work. Won't the INFO command just return the directory of the file I type the formula in? Or the macro in? Here is what I have:
1. File1.xls - is linked to File2.xls and File3.xls
2. File2 and File3 are saved to w: on my laptop...others will have them on y:, z:, etc...

So I need a way to break the links regardless of what directory letter is being used.

I thought to put the INFO("directory") command in the linked files and than send that location to File1, but when I type it in File2, it gives me the location of File1 (my desktop).

Any ideas?

Originally Posted by Neo1824

Click to expand...

Thanks. I'm trying to figure out how to make this work. Won't the INFO command just return the directory of the file I type the formula in? Or the macro in? Here is what I have:
1. File1.xls - is linked to File2.xls and File3.xls
2. File2 and File3 are saved to w: on my laptop...others will have them on y:, z:, etc...

So I need a way to break the links regardless of what directory letter is being used.

I thought to put the INFO("directory") command in the linked files and than send that location to File1, but when I type it in File2, it gives me the location of File1 (my desktop).

Any ideas?

The simple macro I wrote in my last post gives you the path of the file, which will vary based on who is running the macro and what letter their share drive is mapped to. So, if you're coworker retrieves the file from their X:\\ drive and you retrieve it from your W:\\ drive the macro will return "X:\\filename.xls" if your coworker runs it and "W:\\filename.xls" if you run it.

Alternatively, would referencing the ActiveWorkbook work?

Code:

Hi,
So Im making like 200 letters in Word and using the mail merge feature to pull the data out of excel. Easy enough.

The problem is in each letter includes a different custom phone number the customer should call. When I see the number in my excel sheet it shows up as (555) 123-4567 but when I actually highlight the cell it shows up as one long number: 5551234567, so when its exported into word it says:

"call 5551234567."

Any ideas how to fix this?

Originally Posted by MrG

Click to expand...

I don't know if this is possible, but I'll give it a try. I have a workbook with 13 sheets - one sheet has FY10 budget data, and the following 12 are monthly expenditure tracking. My hope is to get the expenditure tracking sheet to create a running total for line items in a single cell. Basically, I want cell xx in September to link to the total of July and August, and I want it to sum these with September's new entry. So if we spend $1,000 in July and August, and $500 in September, I want to be able to key 500 in the September xx cell and have Excel automatically combine it with the prior $1,000 to give me $1,500. The problem I'm having is that I have no idea how to make this happen in single cell. I can do it in multiple cells if need be, but it's a lot of data to roll over each month, so I'm trying to limit the number of cells I add.

Any ideas?

Originally Posted by dsgNYC

Click to expand...

It's possible to show a running tally, but it may not look exactly how you want it to look. It'll really depend on how you're setting up your data on each worksheet. Are you going to be keeping a running tally down each worksheet for each month's expenditures showing the expenditure descriptions and amounts and dates? At least in my mind, the setup would look like this:

Code:

Expenditure Desc. Amount Date

Misc. 100 1/1/2009
Office Supplies 200 1/1/2009
Office Supplies 300 1/2/2009​

I'm having trouble understanding what you want to happen, because I can't understand why you'd want what I think you want to happen...to happen.

Originally Posted by Davidko19

Click to expand...

Hi,
So Im making like 200 letters in Word and using the mail merge feature to pull the data out of excel. Easy enough.

The problem is in each letter includes a different custom phone number the customer should call. When I see the number in my excel sheet it shows up as (555) 123-4567 but when I actually highlight the cell it shows up as one long number: 5551234567, so when its exported into word it says:

"call 5551234567."

Any ideas how to fix this?

Haven't used this feature before, but what I think is happening is that you have the phone number format turned on for the cells you're using. This makes every cell that contains ONLY 5551234567 read (555) 123-4567. The good news you likely have only phone numbers in the cells in question and that makes it easier to replace them to include the formatting you would like in the cell.

Highlight the cells that have the phone numbers, right click, select "format cells", select general and click OK. Use the following formula to create a column of phone numbers that do not need the formatting to read correctly (A1 is your first phone number, drag down assuming you have them in a column):

="("&LEFT(A1,3)&") "&MID(A1,4,3)&"-"&RIGHT(A1,4)

Highlight the new column and right click and select copy. Highlight the old column, right click and select "paste special". Select "values" and click OK.

Then try to do what you were doing. HTH. Save first in case of an f up.

Originally Posted by CunningSmeagol

Click to expand...

Haven't used this feature before, but what I think is happening is that you have the phone number format turned on for the cells you're using. This makes every cell that contains ONLY 5551234567 read (555) 123-4567. The good news you likely have only phone numbers in the cells in question and that makes it easier to replace them to include the formatting you would like in the cell.

Highlight the cells that have the phone numbers, right click, select "format cells", select general and click OK. Use the following formula to create a column of phone numbers that do not need the formatting to read correctly (A1 is your first phone number, drag down assuming you have them in a column):

="("&LEFT(A1,3)&") "&MID(A1,4,3)&"-"&RIGHT(A1,4)

Highlight the new column and right click and select copy. Highlight the old column, right click and select "paste special". Select "values" and click OK.

Then try to do what you were doing. HTH. Save first in case of an f up.

EDIT:

Correction, it worked!!! I account for the right row, but not the right column. Number was in P2, not A2. Thanks man, this is awesome. Reps to you.