Posts by Phil

    Hi all,

    I've got a tricky sorting issue and can't find any reference to this type of issue in previous post's so here goes.

    I have information from several forms that update a summary report. This information comes into 3 lines. Meaning the first row is the critical data and the proceeding 2 rows are support info to the first line. Now I’d like to have a vb procedure that will sort the entire workbook yet keep the 2 support rows directely under the newly sorted critical rows.

    I thought an array sort could be the way to go but, I’m having trouble getting it to work.

    I hope this makes sense. For reference I’ve attached the file.

    Thanks in advance for any guidance provided.


    Sorry for the late response for I have been traveling and unable to participate.

    It does however appear that you all were able to resolve this particularly challenging post. Thanks to all for all the help.

    One thing that I will add is my distain for using a double click event handler to call up the calendar. My preference is to have it come up automatically when a cell that is formatted as a date is selected.

    I do this by adding the following event handler in the sheet module itself.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.NumberFormat = "m/d/yy" Then CalendarFrm.Show
    End Sub

    And as was pointed by our seasoned veterans…this tool was designed to work with or without the cells being merged and ocx is not an issue for this calendar is totally VBA driven and completely devoid of any connection to any activeX control or any type of ocx.

    Kindest possible regards to all and thanks again for the help.


    Hi kundepuu,

    This method would print 10 copies of each sheet at a time before going onto the next sheet in the workbook. Someone would then have to collate them together.

    From the original post I thought the goal was to collate the pages as they are in the workbook.

    I may have miss read...

    Kindest Regards,


    Okay let's try a couple of things.

    If you are using something like...

    ActiveWorkbook.PrintOut Copies:=1, Collate:=True

    as youre syntax to print the entire workbook then try...

    Dim ws As Worksheet
    For Each ws In Worksheets
    ws.PageSetup.PaperSize = xlPaper11x17
    ws.PrintOut Copies:=1, Collate:=True
    Next ws

    This will print each page individually. Now if your wanting multiple copies to print then the following should do the trick...

    Dim ws As Worksheet
    Dim i as Interger
    For i = 1 to 10 '''Whatever is the total number of copies to print'''
    For Each ws In Worksheets
    ws.PageSetup.PaperSize = xlPaper11x17
    ws.PrintOut Copies:=1, Collate:=True
    Next ws
    Next i

    Have not tested but seems pretty sound.

    Good luck let me know how it works.

    Best Regards,


    Ivan, you da man!!!

    Worked great. I feel like an idiot for I've used that syntax before and not sure why I got the block last night.

    I'll blame it on the beers.

    Thanks buddy you’re the best.


    Hey Guys,

    I not sure why I'm can't figure this out but I can’t, Help please...

    I have several activex controls (checkboxes) in an spreadsheet and I'm trying to change a value of an checkbox to true or checked from another sheet. I've treid the following and several variants thereof:

    '.CheckBoxes("CB_" & (Item)).Value = True
    .Shapes("CB_" & (Item)).OLEFormat.Object.Value = 1 'Checked

    Obviously I'm using an with statement that refers to the workbook and sheet before the call. Either way I cannot seem to affect the checkbox.

    Any ideas???


    Hello Matrixman,

    If you could post a little more information it would make it helpful in order to help.

    Such this app. working on a network? or posting the code your currently using could help.

    Pass this on and we can assist.

    Best Regards,


    Hi Paul and welcome to the board.

    Wow, your kind words were quite uplifting. It's nice to be recognized by others especially your peers. However, I cannot take complete credit for this work. I, like you have had nothing but troubles publishing apps using the activeX date pickers. After exhausting every avenue to make them work I decided to build my own, which has been working for me very nicely. However, it is because of others in this forum that this Calendar is what it is. Meaning others like Ivan, Dennis and others did contribute and make it more then I could have on my own.

    Anyway…thanks for the kind words.

    Take care and the very best regards from Texas.


    Thanks Richie,

    Perfect! I should have thought of that...One of those simple things that one (or I) get stuck on.

    Thanks a million.


    PS...Thanks, Will for deleting the other post and glad to hear your daughter is doing better.

    Hello all,

    Does anyone out there have the syntax that would show a userform that resides in another workbook? I have the userform in a workbook that is open and hidden that contains this userform and it will operate on the active workbook that I'm calling it from.

    Any help would be appreciated.


    PS...If you see this post in the Access forum I goofed... (s'ok I killed it, regards, Will :) )

    Hi Pojic,

    Although Will and Dennis always have great input perhaps the attached file can be of some benefit to you.

    I developed the attached calendar date picker in vba because I ran into so many issues regarding activex controls and weather they are registered on different users machines. The vba calendar can be dropped into your app as a user form and seems to be flexible enough to handle a variety of situations.

    Hope it helps, it has preformed well for me.


    Hi Dennis and thanks for interrupting your busy schedule to look into this.

    However, to code that you had provided is exactly what I've been using (I guess that I got it from Jim Rech originally).

    It does work well but for some reasons when the text gets to long it stops wrapping and expanding. At that point one has to manually insert carriage returns at the end of each line of text and make the height taller.

    I guess I'm getting caught up with making it work the best that it can and save my users some trouble.

    Thanks again for the help...and my best to you and your family.


    Hello all,

    I’ve had an issue for some time with this particularly tricky issue. It seems that MS Excel has a problem “Auto Fitting” a row height on areas within a spreadsheet that have cells that are “Merged”. I’ve used the code below with some success however there are times that it fails to work. When I say that I mean on the same sheet works sometimes and not others, it fails maybe 5% of the time (not acceptable). Additionally, the word warp in this area will sometimes quit. This seems to happen when one enters an excessive amount of text.

    The Goal…When a user enters text into a field that consists of several merged cells on the same row, that area’s row height would expand to fit the entire text and the text would wrap within the same area.



    Dim CurrentRowHeight As Single, MergedCellRgWidth As Single
    Dim CurrCell As Range
    Dim ActiveCellWidth As Single, PossNewRowHeight As Single
    If ActiveCell.MergeCells Then
    With ActiveCell.MergeArea
    If .Rows.Count = 1 And .WrapText = True Then
    Application.ScreenUpdating = False
    CurrentRowHeight = .RowHeight
    ActiveCellWidth = ActiveCell.ColumnWidth
    For Each CurrCell In Selection
    MergedCellRgWidth = CurrCell.ColumnWidth + MergedCellRgWidth
    .MergeCells = False
    .Cells(1).ColumnWidth = MergedCellRgWidth
    PossNewRowHeight = .RowHeight
    .Cells(1).ColumnWidth = ActiveCellWidth
    .MergeCells = True
    .RowHeight = IIf(CurrentRowHeight > PossNewRowHeight, _
    CurrentRowHeight, PossNewRowHeight)
    End If
    End With
    End If

    Hello again,

    With regard to the class issue try using "Worksheets("PEOPLE")." if front of the range text.

    ie...Worksheets("PEOPLE").Range("A" & ((max_people) + 3), "A65536").EntireRow.Hidden = True

    Regarding the refresh issue, unless you have set the screenundating to false you shouldn't have an problem with the refresh. If you have set screenupdating to false somewhere earlier in the sub then use your line Application.ScreenUpdating = True and that should do the trick.

    Let us know how it goes.