Posts by misscrf

    Re: Count Text File Lines

    Quote from Kenneth Hobson;375912

    In these routines, I show how to get the count of lines and contents. Change the value for myPath in the Sub test(). If the file does not exist, the count will be shown as 0 and the contents will be shown as "NA".

    This is a great solution! Thank you for posting it. Is there any way, in the TXTStr function to not count the row if it is blank? I am counting the lines, before I import them to a temp table, and I want to be able to compare the count from before import to after import.


    Re: Data Sort with Protected Worksheet

    Thank you for the reply uan. This is the same solution that I found as well, and you are right that it does allow the autofilter functionality. If you have a couple of other columns, though, and try to sort, you will see that it still does not allow sorting. My boss also found that if you have a sheet with subtotals, the autofiliter will only work up to the first blank cell, anything after that will not filter.

    Just some things to point out.

    Re: Data Sort with Protected Worksheet

    Hi, I have xp and I am having the same trouble. I want to protect every sheet in a workbook. I want users to be able to sort, but not to copy the data, or change it.

    I go to Tools | Protection | Protect Sheet

    Then I have checked the following things:

    pivot table reports

    When I then keyed a password and reentereed, I could not sort or use autofilter.

    I went back and check the following as well:
    select locked cells
    select unlocked cells
    format cells
    format columns
    format rows

    It still will not let me sort, and autofilter is grayed out. Can someone please help?


    Re: Highlighting rows and columns for presentation

    p.s. I also tried:
    ActiveWindow.GridlineColor = vbBlack[/code]

    It gave me black borders, and the diagonal lines that cross out the cells (all over the sheet.)

    I am close though! I just need to have it give me those borders without the diagonal line.

    Anyone? Anyone?

    (Bueller? Bueller?)


    Re: Highlighting rows and columns for presentation

    Thank you both for the responses. I like your code, badger, because I can have a button on the person workbook that will stay with any that I open.

    My only issue is that when I hit the button, and it makes everything not selected white, I lose the grid. I need to be able to see the lines between each cell.

    I tried this:

    ActiveWindow.GridlineColor = RGB(255, 255, 255)

    but it didnt work.

    Any thoughts?


    I have a series of excel spreadsheets. As one person is talking, I need to hightlight the cell or row that they are talking about. I am looking for a clean way to do this. I am not sure if using the paint can is the best method (and then undoing.)

    Is there anyway to change the color that the row/column becomes when it is just selected? It is kind of purple/translucent. Can we change that to a yellow?

    Thanks for any suggestions!


    Re: Forms

    Let me know what you find.

    I have seen these things that you can try with that type of textbox:

    Right click the textbox and choose properties.

    1) (you may have to play with this setting) - on the size tab, set a height and width to the textbox, and check the box to lock the aspect ratio. You may have to adjust the width and height to fit the amount of characters you want. Understandning a person can fit more iiiiiiiiii's than bbbbbbbb's.

    2) on the textbox tab, uncheck the box for wordwrap.

    3) on that tab, uncheck the box to resize autoshape to fit text.

    Hope that helps.

    Re: Forms

    In Word textboxes, open the properties and check 2 settings:

    1) set your maximum length. If it is 0, I believe that actually means on maximum to the length. It must be 1 or more (probably up to 256)

    2) ensure that multilines is set to false, and wordwrap should probably be false as well.

    Hope that helps.

    Re: Macro page breaks not row header value footer

    Yeah, I am sorry if I explained the first part badly. I have the first row repeat on every page. It is not in a header. It is just that my code to insert a page break based on the value of column B is getting tripped up on the first page because of this repeating first row.

    The whole thing works great, each page is all the same value in B, the problem is that I get a first page with just row 1, because that row is set to repeat. It may not even be because it is set to repeat. It is probably just because it is a different value then the next, so the page breaks.

    The second part, I do want to believe is possible, I just don't know how to word the code. I would think to write something that would trick the system.

    Like use the formula to find the first value in the B column on that page and put that in the footer.

    basically use a formula that will find the value of any B cell on the page - any B on any 1 page will be the right value.

    Hi, I have seen some posts that are similar, but no one seems to have the problem that I am having. I will post my macro in this, for anyone that is interested. Be warned that I am not advanced at Excel macros, so it is a messy one.

    My users get a csv file every month, and we have to clean it up. This macro does that.

    My last issues are this:

    1) having the spreadsheet create page breaks whenever the value in column B changes. Below is just that code.

    col = 2
    LastRw = ActiveSheet.UsedRange.Rows.Count
    For X = 2 To LastRw
    If Cells(X, col) <> Cells(X - 1, col) And Cells(X, col) <> Range("B1") Then
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(X, col)
    End If

    The problem that I am having is that, it is breaking on the row header for the first page. I have row A repeat at the top of every page. It does make sense in the code that this value changes, so it makes a page break. Can anyone help me to adjust my code so that it will ignore the first row when it makes the page breaks?

    2) I want to take the value in column B, as it will be the same for any given page due to the above page breaks, and put that in the footer.

    I have commented out the code that I was having fun trying. The idea is that column B is a box number, and I want to have that box number in the footer, so that it is easy to see on the sheet. Here is my page setup code for headers and footers.

    As I said, I commented out the right footer where I would put this code. Any help would be great.

    With ActiveSheet.PageSetup
          .CenterHeader = "Our Form"
          .LeftFooter = Date
          .CenterFooter = "Signature __________________________________"
          ' this is where I want the value -->   .RightFooter = "Box Number: " & Column("B:B").Value
        End With

    For anyone who is interested, here is my entire messy code. I started off with what we had, recorded portions to do more, and added bits and pieces together.

    It is not organized at all, but it works!



    Re: Request for help with printout code

    can I ask what this means?
    ActiveSheet.PageSetup.CenterFooter = Cells(q, 1)

    I am working on a macro that breaks each page based on the value in column B. My second part is to have a footer say the value in that column, as it will be the same for each page. When the page breaks for the next value, the footer would change as well.

    Is that what this will do?


    Re: VBA: Adding Conditional Page Breaks

    I have tried 2 other attempts including this. Everyone of them works good, but I keep having the same problem. I have row A repeat at the top of every sheet. When I run this, my first sheet is always a blank sheet with jsut the header row. It breaks on row a. Grrr. Can anyone help?

    Other than that it works great!

    Dim X As Long
    Dim Rg As Range
    On Error Resume Next
    Set Rg = Selection 'Range("C4:C15")
    For X = Rg.Cells.Count To 1 Step -1
    If Rg.Cells(X) <> Rg.Cells(X).Offset(-1) Then
    ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Rg.Cells

    End If
    Next X
    original page break -

    For i = 2 To ActiveSheet.UsedRange.Rows.Count
    If Cells(i, 2).Value <> Cells((i - 1), 2).Value Then
    Cells(i, 1).PageBreak = xlPageBreakManual
    End If


    Re: SOLVED Insert Page Break Macro required

    This works for me, except that it is giving me a first page of just the row headers. I have row A repeat on every page. Is there a way that when I set this, I make sure it doesnt give me a page of just the row headers?

    This is great, thanks.

    Re: macro to record page setup

    here is my code, sorry for not including it before. When I use the recorder, I have been able to add things like the changing the row header fields, formatting the fields and such. When I used it and went to page setup to check gridlines and type stuff in the headers and footers, it never recorded. I tried it 3 times, and it never recorded any of it.

    I have gotten a lot of it by searching online and in help. I will post it below, with where I am at now.

    Thank you both for responding.

    Right now the right footer is the only thing giving me problems. I want that page 1 of 5 deal in the footer, but I cant figure out the code for page and pages. It is not the same as in the footer on the front end. I tried that first.

    After that, I will work on the copy and paste to a new page, closing the csv file with no save.

    The "Nice Title" is because I have to show it to the people, so that they can tell me if they want a title, and what they want it to be.

    Thanks for all your help!

    I know I can conquer this macro!

    I was shown a macro that someone before me created. Now I have been asked to add to it. I guess the first step is the export the macro/module, so that I can eventually add the newely requested code to it.

    The code is to take a csv file, that is received, and format it.

    At this point, it formats the columns and the margins.

    Now I want to add some more to this. I want to change field names. I recorded that, and it worked fine.

    Next, I want to set the page set up to repeat the first row on every page, specify the headers and footers, and check for gridlines to be shown. When I record this, nothing is recorded. I go to record a new macro, and then start moving my cursor as I want the macro to act. I go to page set up and execute those tasks. When I stop the recorder and view the code, there is nothing there but the header comments with the time stamp that I created the macro.

    My last issue, is that I would like to, either in the beginning before I format, or at the end, copy the worksheet to a new book, and close no save on the csv file.

    Can anyone please help me troubleshoot this?


    That subject line is annoying. I couldnt even type in Excel. .Whats that all about? :nono:

    Anyway. I have an application in Access. I am working on some new reports. One I would like to do is a statistical report with charts.

    I only really know how to do charts in Excel, but I don't understand how to link them back to an Access report and have them refresh their data on open.

    I would like to keep this all in Access, as the rest of the application is in Access.

    I am looking for some help on how to create a bunch of charts (like 5) for 1 statistical report. I would also like to allow the user to specify a date range for the data, and to have that qualify for all of the charts.

    To give you an idea of the charts here is a run down:

    100 people apply for a job at my company.

    I want to see a chart counting who applied to what office, one chart counting who applied by what application source(, walkin, email), 1 chart counting who applied to what department (IT, finance, HR), etc.

    Thanks for any help you can give. :thanx:

    p.s. don't try to put "Access application - charts in Access or Excel" as a title, it will get deleted on you!