Posts by GuyGadois

Re: Ignore Plotting Zeros Or Empty Cells As Zero In Chart

Quote from AAE

Try using =NA() instead of =""
If having #N/A in the cell is objectionable, use conditional formatting to hide it.

You could also hide the columns or rows that are empty.

Many thanks for your reply. I can't hide the rows that are empty because the chart is only one part of the sheet and it would effect too much. But, I like the #N/A solution. Here is what works, so far (with little check so far)

=IF(Custom_Chart_Variable=Custom1_Header,IF('2009 Log'!AD348="",NA(),'2009 Log'!AD348),IF(Custom_Chart_Variable=Custom2_Header,IF('2009 Log'!AE348="",NA(),'2009 Log'!AE348),IF(Custom_Chart_Variable=Custom3_Header,IF('2009 Log'!AF348="",NA(),'2009 Log'!AF348),IF(Custom_Chart_Variable=Custom4_Header,IF('2009 Log'!AG348="",NA(),'2009 Log'!AG348),""))))

Seems funny Excel doesn't have an ignore zeros solution built in. I thought previous version of Excel had this. I might be wrong, though.

Guy

I am having a problem getting Excel to not plot zero values or empty cell values in a line chart.

The details:

I am attempting to offer spreadsheet users a way to pick which data is charted in a simple line chart. On the worksheet their is a drop box that allows a user to choose which column they want charted (the choices is "=Admin_Custom_Field_Names" which are 4 heading names a user picks). So, the user picks one of the heading names from a drop down validation list and then the chart updates. Here is what I have behind the scenes.

The chart data is a column with this formula:

=IF(Custom_Chart_Variable=Custom1_Header,'2009 Log'!AD3,IF(Custom_Chart_Variable=Custom2_Header,'2009 Log'!AE3,IF(Custom_Chart_Variable=Custom3_Header,'2009 Log'!AF3,IF(Custom_Chart_Variable=Custom4_Header,'2009 Log'!AG3,""))))

Basically, that formula checks to see if the user picked column 1,2,3 or 4 to be charted and uses the coorosponding data to chart.

Now, the problem is that even if the corosponding column has no data entered then the formula produces a "0" and the chart plots the point 0. So I fixed that and had it enter a "" if the column data was empty but the chart stills an empty cell as 0. Then, I tested removing the formula above from a few cells and noticed that the chart does not plot those cells. So, eventhough the cell has no value (or a "" value) Excel DOES chart it if there is a formula in it. If the cell is truely empty then it does not chart it. I am failing to find a way in Excel 2007 to ignore plotting zeros or cells that equal "". How can this be accomplished? The other twist is that I desire this to work in Excel 2003 and 2007 for PC and Excel 2004 for mac.

Many thanks,

Guy

Re: Format & Find Date Added To TextBox

Dave, I never used DateSerial before. I will look into that more. To make the code work in my workbook I added a check to make sure the user preference is indeed dd/mm/yyyy. I think it works (at least a couple of trial worked so far. Thanks for your assistance).

If Worksheets("Admin").Range("Custom_Date_Format") = "dd/mm/yyyy" Then
        If IsDate(TextBox1) Then
            TextBox1 = Format(DateSerial(Year(TextBox1), Month(TextBox1), Day(TextBox1)), "dd/mm/yyyy")
        End If
    End If

I have a log that I am trying to make compatible for international users. They enter bike rides via a form. When the user hits submit Excel finds the date and then posts the ride info.

The problem is that if I format the date textbox (textbox1) as international the date inputted in textbox1 isn't being found in the column.

Here are the snippets of code from the form (other parts of the code were excluded because they shouldn't effect this issue.

Code when the form initializes

Private Sub UserForm_Initialize()
    If wb.Worksheets("Admin").Range("Custom_Date_Format") = "mm/dd/yyyy" Then
        TextBox1.Value = Format(Date, "mm/dd/yyyy")
    ElseIf wb.Worksheets("Admin").Range("Custom_Date_Format") = "dd/mm/yyyy" Then
        TextBox1.Value = Format(Date, "dd/mm/yyyy")
    End If
End Sub

Code part way through when submit button is pressed. This code matches the dates and tries to find the corresponding date. IF the date is inputed in international format (dd/mm/yyyy) and the text box is formatted as "TextBox1.Value = Format(Date, "dd/mm/yyyy")" and the Date_Col is formatted dd/mm/yyyy why can't the date in textbox1 match the dates in Date_Col. What it does match in this case is the US date format. For instance. I enter 12/01/2009 and it finds Dec 1 when I want it to find Jan 12, 2009.

Dim Date_Col, Date_Row, Last_Row, I, Selected_Row As Integer
    Dim Selected_Date As Date
    Dim ws As Worksheet
    
    Set ws = Sheets("2009 Log")
    
    Date_Col = Worksheets("2009 Log").Range("column_h_date").Column
    Date_Row = Worksheets("2009 Log").Range("column_h_date").Row
    Last_Row = 1500
    
    Selected_Date = TextBox1.Value
    Worksheets("2009 Log").Select
    
    For I = Date_Row + 1 To Last_Row
        If (Cells(I, Date_Col) = Selected_Date) Then Cells(I, Date_Col).Select
        If (Cells(I, Date_Col) = Selected_Date) Then Selected_Row = I
        If (Cells(I, Date_Col) = Selected_Date) Then Exit For
    Next I

Date_Col is formatted using one of these depending on what the user picks...

Sub Date_Change_Intl()
    Set rngBB = ThisWorkbook.Worksheets("2009 Log").Range("B:B")
    rngBB.NumberFormat = "dd/mm/yyyy;@"
    Worksheets("Admin").Range("Custom_Date_Format") = "dd/mm/yyyy"
End Sub
Sub Date_Change_US()
    Set rngBB = ThisWorkbook.Worksheets("2009 Log").Range("B:B")
    rngBB.NumberFormat = "dddd dd;@"
    Worksheets("Admin").Range("Custom_Date_Format") = "mm/dd/yyyy"
End Sub

Any help why this code doesn't work?

Guy

Re: Order Columns & Tabs Based On User Input

Quote from Dave Hawley

Guy, why not detail what you final aim is, perhaps there are better ways.

Dave, Thanks. The final aim is to make my spreadsheet "totally" customizable. Some users like to have the column which contains "Distance" second and "Time" third. I want them to be able to reorder them even though the spreadsheet is locked (in other words they can't do it manually). Also, I have so many tabs that the user has to scroll to see them all. Many users would like to reorder the tabs so they can have the most used tabs on the left.

Warm regards,

Guy

Re: Weekly Chart With Ability To Change Start Date

Thanks for your messages. I have avoided using Pivot Tables in the spreadsheet because of the problems when people with Macs try to use them. I may revisit that idea and see if Macs still have difficulties with pivot tables.

Another idea is to make the weeks always start on Monday and just hardcode the data as two separate colums (one for date and one for mileage summary). It is a work-around and not my first choice. I would rather do this with smart coding.

Guy

How would I reorder columns & tabs based on a form where the user can put them into order that they would like the columns? I forsee some type of form where the user can dsignate the order and then start a macro that will sort both the columns and tabs.

Possible?

Regards,

Guy

Re: Weekly Chart With Ability To Change Start Date

Quote from Dave Hawley

No time for all your questions, sorry. But to make a Dynamic Named Range to include only 1 week from a starting date of choice, do this.

1) Use any cell to hold a Data Validation List of valid dates and then Name this cell StartDate

2) Create a Dynamic Named Range for the chart date Series and link it to the StartDate. E.g.

=OFFSET(Match(StartDate,$A$2:$A$100,0),0,0,7)

Or, you could try a PivotChart.

Display More

Dave,
Thanks for your reply. I think your solution only shows the sum from one date down seven days. That will work if I am only trying to sum 7 days but there are two problems.

1. I have to do every seven days through the year (weekly chart for entire year)

2. If another day is added to the week will this expand to include that new day?

I may be implementing it wrong so your solution may be the right one with my mistakes.

Guy

I have a simple bike log that lists out each day of the month in rows with a summary row between months. Like this...

January Summary
Jan 1
Jan 2
Jan 3
....
Jan 31
February Summary
Feb 1
Feb 2
Feb 3
....
Feb 28

and so on. Each column has a data field where I enter in miles and distance and so on. I frequently add rows if I do two rides in a day so two rows may start with "January 23rd". I would like to have a chart that shows me my weekly ride summary. But I have some issues:
1. With the field summary I don't know the best way to do a chart that doesn't include the summary (the summary data would throw the chart way off)
2. If I have multiple rides in a day I don't know how to make the week include those days
3. I may want the week to start on Monday and not Sunday. Is it possible to have the user choose and have the chart change automatically?

Lots of stuff here. I hope it makes sense.

Guy

Re: Copy And Paste Excluding Formatting And 'protection'

Quote from Dave Hawley

Code

Range("MyRange").Copy
    With Sheet2.Range("A1")
           .PasteSpecial XlValues
           .PasteSpecial XlFormulas
     End With
Application.CutCopyMode=False

Dave, thanks! IF I wanted to also include the protection characteristic what would I use?

Is there a place on your site that lists the PasteSpecial options?

Regards,

Dan

I have the following code from a form. It works just fine copying named ranges from one sheet to another. The probloem now is that I don't want to copy the characteristic of the formating or whether they are protected or not. The originating spreadsheets have cells that are protected. I am trying to copy those values over to the new sheet and paste their values or formulas but not their formatting (background color) or the fact that some of the cells are 'locked'. I want the new sheet to have the values and formulas but not the be lock for the user. Is this possible with the current way I do the code or do I have to rewrite it? How would you approach this?

Thanks,

Guy
PS: Happy Holidays to All! :catinthe:

Private Sub CommandButton1_Click()
    
    Import_Data_Form.Hide
        
Run "NPA"
        
    Set b = Selection
    ad = b.Address
     
     '   Local Variables
    Dim wkbDataFile         As Workbook
     
     '   Let user select source datafile
    Call UserSelectFile_WOpen(wkbDataFile)
    If wkbDataFile Is Nothing Then
        MsgBox "User did not select a workbook to open"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    Set wb = ThisWorkbook
    
    Application.DisplayAlerts = False
    
    
    'Log Data
    wkbDataFile.Worksheets("2009 Log").Range("Column_Type_Of_Ride").Copy Destination:=wb.Worksheets("2009 Log").Range("Column_Type_Of_Ride")(1,1)
    wkbDataFile.Worksheets("2009 Log").Range("Column_Time_of_Day").Copy Destination:=wb.Worksheets("2009 Log").Range("Column_Time_of_Day")(1,1)
    wkbDataFile.Worksheets("2009 Log").Range("Column_Route_Name").Copy Destination:=wb.Worksheets("2009 Log").Range("Column_Route_Name")(1,1)
    
    On Error Resume Next
        wkbDataFile.Worksheets("2009 Log").Range("Column_Ride_Style").Copy Destination:=wb.Worksheets("2009 Log").Range("Column_Ride_Style")(1,1)
    On Error GoTo 0
    
Application.DisplayAlerts = True


wkbDataFile.Activate
Run "PA"
wkbDataFile.Worksheets("2009 Stats").Select


wb.Activate
Run "PA"
wb.Worksheets("2009 Stats").Select
        
Application.ScreenUpdating = True
Application.CutCopyMode = False


End Sub
 
Sub UserSelectFile_WOpen(wkbDataFile As Workbook)
     
     '   Local Variables
    Dim strSelectedFile     As String
     
     '   Let user select a workbook
    strSelectedFile = Application.GetOpenFilename(FileFilter:="XLS Files (*.xls), *.xls", Title:="Select Desired Data File", MultiSelect:=False)
     
    If strSelectedFile = "False" Then Exit Sub
     
    Set wkbDataFile = Workbooks.Open(strSelectedFile)
     
End Sub


Private Sub CommandButton2_Click()
    Import_Data_Form.Hide
End Sub

Re: Insert Rows And Copy Formula Cells

Quote from Ger Plante

Dave - OMG!!!! I cant believe you used a loop!!! ::D

Here is a loopless alternative. Not sure if it would be any more efficient. And it would probably copy formatting too which you dont want.

Code

Sub MyMacro2()


    
    Rows("2:3").FillDown 'copy contents of entire row
    Rows(3).SpecialCells(xlCellTypeConstants).ClearContents 'remove anything that isnt a forumla
        
End Sub

Ger

Display More

Ger, nice catch. I understand your code will work with set rows like "2:3" but will it work with unsing variable set rows like I have (ie cRow and cRow+1)? The user picks a row location where they want to add a row and fill that new row with all the formulas from the row being copied (but, as you note, none of the data).

Man, this little macro is giving me all sorts of fits.

Thanks in advance

Guy

Re: Insert Row And Copy From Line Above If There Is A Formula

Quote from Dave Hawley

Use the SpecialCells Method and restrict to ONLY formulas.

.End(Xldirection) will NOT stop at hidden rows or columns.

Dave, thanks for your reply. Interesting idea. I have never tried that way. This is the code I developed from your idea...

For j = 1 To Cells(1, 255).SpecialCells(xlCellTypeFormulas)
                If Cells(cRow, j).HasFormula Then Cells(cRow, j).Copy Cells(cRow + 1, j)
            Next j

It does the same thing because I think I am doing it wrong. So I tried this way but it still does the same thing.

For j = 1 To Cells(1, 255).SpecialCells(xlCellTypeFormulas)
                Copy Cells(cRow + 1, j)
            Next j

So, what am I doing wrong? How do I get excel to copy rows even when hidden?

Thanks much in advance.

Guy

Re: Insert Row And Copy From Line Above If There Is A Formula

Ger,
Thanks for the advice. That helped a lot. I was testing the new row for formulas but it was a new row and empty. The fixed code is below for reference.

Now, there still is a problem of it not finding formulas for most of my sheet - the part where the columns are hidden. I have 1/2 my sheet hidden. The macro does not copying and paste them as I think it should. Could this be because they are hidden? Could it be because I have from column AP to the end column hidden? I have Column J & L also hidden but the macro DOES copy those formulas. Why would it not copy those formulas of the hidden set that goes from AP to the end?

On a hunch I tried just hiding 5 of the columns that didn't copy and ran the code again. It copied fine. Next I unhid the last column and just hide AP over two the second to last column. The code did NOT copy the info. So, it looks as though the code works when some of the columns are hidden but after a certain amount of hidden columns then it no longer works. What am I doing wrong?

Guy

I am trying to finish off a form that allows a user to insert a row below the selected cell and copy the formula from the line above if one exists. The code inserts the line but does not copy the formulas if they exist. any help is appreciated.

Guy

Application.ScreenUpdating = False


    Dim cRow
    Dim j As Long
    
    cRow = ActiveCell.Row
    
            With ActiveCell
                .EntireRow.Insert
            End With
                 
            For j = 1 To Cells(1, 255).End(xlToLeft).Column
                If Cells(cRow, j).HasFormula Then Cells(cRow, j).Copy Cells(cRow + 1, j)
            Next j
             
            Application.ScreenUpdating = True
            Application.CutCopyMode = False
            
    Unload Add_Date_Form
    
End Sub


Private Sub CommandButton2_Click()
     Unload Add_Date_Form
End Sub






Private Sub UserForm_Initialize()


    Dim cRow
    
        cRow = ActiveCell.Row ' remember the current row
        TextBox1 = Cells(cRow, Range("column_date").Column).Value


End Sub

Re: Change Charts Backround Color Via Drop-Down

Thank you. That seems to do the trick for the background. Is there a place where I can find all the chart properties (ie Chart.ChartArea.Interior) so I can change exterior, lines, fonts and so on.

Regards,

GG

I have a form that the user, of course, enters in various data points. One bit of data is choosing a bike route they ride. Based on what route they choose I would like the other textboxes on the form to populate with the corosponding data.

On one of the worksheets I have a table the user fills out that has the name of the route, mileage and elevation gain (20 routes).

When the form is engaged the user can choose between what route they want. Now, how can I, if I can, then have the data populate on the other textboxes for the corosponding data from that matching ride?

Thanks much in advance

Guy Gadois

I have a chart where half the users want a black backgroud and half want a gray background. Is there a way I can change the chart colors based on a textbox value? I would have the user choose a value (say, "Dark Background or Light Background") and then, upon commencing the macro it changes the chart. Is this possible? What is the best way to approach this and is there a place I can learn how to do this?

Thanks,

Guy

Re: Validate TextBox Date Entry For Year

Great advice! Thank you as always, this site and its contributors rock!

Guy

Re: Validate TextBox Date Entry For Year

Dave, thank you for your reply. I followed the instructions to add Calendar Control (http://www.ozgrid.com/VBA/excel-calendar-dates.htm) but there isn't an option for the Calendar (Calendar Control 10.0). There is nothing that says Calendar. I use Excel 2003 for PC. What am I missing?

Also, this spreadsheet is being distributed to MANY users. Will it work for everyone or just folks who have that control installed on their system?

Thanks,

Dan