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.

Phil

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.

Phil

Sorry for the late reply...I've been traveling and will not be back in my office until Monday.

I'll try to take a look soon.

Phil

Hi kundepuu,

Quote

Why not just like this

For Each ws In Worksheets
ws.PageSetup.PaperSize = xlPaper11x17
ws.PrintOut Copies:=10, Collate:=True
Next ws

Display More

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,

Phil

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,

Phil

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.

Phil

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???

Phil

Hello Matrixman,

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

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

Pass this on and we can assist.

Best Regards,

Phil

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.

Phil

Thanks Richie,

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

Thanks a million.

Phil

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.

Phil

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.

Phil

So what is the solution???

Phil

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.

Phil

My good friend Dennis,

Glad to hear from you, haven't seen you around much. I hope your site is coming along well.

Regarding this solution...I as always appreciate any help that you or anyone else could provide.

Thanks again,

Phil

Second Bounce to Top...I have high hopes that someone has faced this and resolved. Any information that can be provided would be greatly appreciated.

Thanks,

Phil

I’m bouncing this back in hopes of getting some kind of response. If someone knows a way please make me aware.

Phil

WOW Derk,

Great explanation...I'm impressed!

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.

TIA,

Phil

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
Next
.MergeCells = False
.Cells(1).ColumnWidth = MergedCellRgWidth
.EntireRow.AutoFit
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.

Phil