Posts by markc

Re: Conditional Format With Vba

Perfect!
Thanks Mikerickson

Hi all

I recorded some code and cleaned it up to apply a conditional format

Sub condi_format_I()
    Dim wbBook As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    
    Set wbBook = ThisWorkbook
    Set ws = wbBook.Worksheets("Summary")
    Set rng = ws.Range("C4:P52")
    
    With rng
        .FormatConditions.Delete
        .FormatConditions.Add xlExpression, Formula1:="=C4<0"
        .FormatConditions(1).Interior.ColorIndex = 45
    End With


End Sub

I would like to apply a custom color index to all cells in range C4:P52 if the value is less than zero.

But when I execute the macro, many cells receive the custom format that do notr match the custom format. Other cells that should receive the custom format do not

What am I doing wrong?

Thanks
-marc

Re: Convert Sumproduct To DSUM

Thanks Guys -

I tried downloading the DSUM samples, I guess I can't figure out how to use with my current worksheet setup.

I've attached a small sample of the workbook
The current sumproduct formula is in cell B15

I formatted the criteria cells a blue interior

Thanks much
-marc

Hi all -

What would be the correct syntax to convert this sumproduct formula to a dsum formula
I can't seem to find any examples that match my exact need

=SUMPRODUCT((Data!$A$2:$A$15998=$B$3)*(Data!$C$2:$C$15998=$A14)*(Data!$E$2:$E$15998=B$10)*(Data!$D$2:$D$15998))

Thanks much
-marc

Re: Run Custom Views From Worksheet`

Thanks Dave -

I would like to give a drop down or other to the user to select the custom view
There are 7 to choose from

• I selected a view from the custom view command button/drop down
• I added your code to a new button
• Seems to return the full view of the data which is also defined as a custom view and appears first in the custom view command list

Thanks for your help!
-markc

Hi all -

I setup custom views for my worksheet
I dragged the custom view command to my menu bar
Everything works great on my machine

But I would like to send to other users and for them to be able to use the custom views w/o having to help them setup on the menu bar

Plus I've had problems in past where custom view tool is not persistent on the menu bar
Sometimes there, sometimes not

Is there a way to add to say cell A1 on the worksheet instead?
I tried dragging it there, but no luck.

Otherwise, is there a simple way to emulate the same functionality?

Thanks
-marc

Re: Table Of Contents With Hyperlinks

Thanks all.
Appreciate all of the replies.

XLScottW's reply did the job nicely after I removed spaces from the tab names.

For example:
Customer Analysis returned the error message
But Customer_Analysis works as expected

Thanks!
-marc

Hi all -

I am attempting to build a table of contents and add hyperlink to each entry
as suggested by jabax

The TOC is constructed properly, but when I click on the hyperlink I receive an error message:
"Reference is not valid"

My code (courtesy Jabax):

Sub Create_TOC()
    Dim wb As Workbook
    Dim wsTOC As Worksheet
    Dim ws As Worksheet
    Dim r As Long
    
    Set wb = ThisWorkbook
    Set wsTOC = wb.Worksheets("TOC")
    
     
     ' Turn the next two lines of code on and off as preferred
     ' Ans =  MsgBox("Do you want to update the Index?", 4)
     ' If Ans = vbNo Then Exit Sub
     
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .DisplayAlerts = False
    End With
     
    r = 1
     
    For Each ws In wb.Worksheets
        If ws.Name <> wsTOC.Name Then
            With wsTOC
                .Range("A" & r) = ws.Name
            End With
             
            If ws.Visible = True Then
                wsTOC.Hyperlinks.Add anchor:=wsTOC.Cells(r, 1), Address:="", SubAddress:= _
                "Start" & ws.Index
            End If
        End If
        r = r + 1
    Next ws
     
    With wsTOC
        .Range("A1").Select
        .Columns("A:A").EntireColumn.AutoFit
    End With
     
    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
    Set wb = Nothing
    Set wsTOC = Nothing
    Set ws = Nothing
End Sub

Thanks
-marc

Re: Pass A Named Range To Another Range

Thanks shg
That did it

Raised a new error though:

PrintIt (rngPrint)

Quote

Run time error '424'
Object required

PrintIt is a subroutine where I am trying to pass the range to

Private Sub PrintIt(rng As Range)


    Range(rng).Select
    ActiveSheet.PageSetup.PrintArea = rng
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = "$A:$D"
    End With
    ActiveSheet.PageSetup.PrintArea = "$A$1"
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&""Arial,Bold""Disney Market Price Analysis"
        .RightHeader = ""
        .LeftFooter = "&8&D"
        .CenterFooter = ""
        .RightFooter = "&8&T"
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = True
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

Thanks
-marc

Hi all -

In my code, I add a name

Names.Add Name:="rngPrntMick", RefersTo:="=Market!$E$2:$N$242"

I then try to assing the named range to another range

Set rngPrint = rngPrntMick

It is on this line execution stops and I receive error:

Quote

'Run time 44':
Object Required

I'm not sure what I'm doing wrong
Any advice?
Full code below
Thanks
-marc

Sub Print_Mick()
    Dim wb As Workbook
    Dim wsMarket As Worksheet
    Dim rngPrint As Range
    
    Set wb = ThisWorkbook
    Set wsMarket = wb.Worksheets("Market")
    
    Names.Add Name:="rngPrntMick", RefersTo:="=Market!$E$2:$N$242"
    
    Set rngPrint = rngPrntMick
    
    Call ShowMick
    Call BeforePrint
    Call PrintIt (rngPrint)
    Call AfterPrint
    wsMarket.Range("A1").Select
    
    Set wb = Nothing
    Set wsMarket = Nothing
    
End Sub

Hi all
I have my cell setup as fraction format

I was able to get 4/3 to display as 4/3 instead of 1 1/3 by changing custom format to ?/? no leading space from # ?/?

However when I enter 3/3, the display is 1/1
Is there a way to display as 3/3?
Must be a number, it is used in mathematical operations

Thanks
-marc

Re: Congratulations Will Riley On Passing The 8,000 Mark

Will -

Thanks for the great posts and the great help!
Congrats!

-marc

Re: Report Manager Incorrect Order

Bump
Does anyone have similar experience with the report manager xla?

Thanks
-marc

Hi all

I have Report Manager all setup
1 Custom View per worksheet
No scenarios
I selected pdf995 as my default printer

I printed the report
A few issues
Each is created as a separate print job (should be 50)
I would prefer 1 print job

The pages are out of order or don't print at all
I reviewed "Sections In This Report" and everything is in correct order

18 items are listed, but only 9 printed
Any ideas? Or other workarounds?

Thanks
-marc

Re: Header Via Vba With 3 Lines

Thanks Roy

Really great stuff
Nice that each item in a separate cell since items 1 and 2 are repetitive and 3 and 4 are unique

Thanks for the great help!
-marc

Re: Header Via Vba With 3 Lines

There are at least 52 pages in the report, possibly more.
Each with unique header
I would like to read the header from a print setup sheet beginning at cell D2 and down until no more Custom Views to be printed

Thanks
-marc

Re: Maximum Number Of Worksheets?

Hey...how...what the heck??
Worked great!!
Lifesaver!!

Thanks
-marc

Hi all

I manually added a center header to my report that take 4 lines
So I would like to repeat this using vba

Line1: Looney Tunes, Inc.
Line2: Bugs Bunny Subsidiaries, Inc.
Line3: Consolidated Rollup
Line4: FY 2007

Furthermore, I would like to read this from cell(s) as at least a 52 page report
and lastly I would like to alternate font sizes

Line1: 18 pt
Line2: 18 pt
Line3: 16 pt
Line4: 14 pt

Has anyone seen anything like this or have some pointers?
Thanks
-marc

Hi all

I am trying to copy a worksheet in my workbook many time inside the same workbook

I currently have 56 worksheets

I tried copying the source worksheet again and designating where the copy should be placed. But no copy is created.

No error messages are received
File is 7.8MB

So far, 15 copies of the sheet
I tried renaming a resultant copy and copying that

Still no duplicate is provided

Any ideas?
Everything I've read, worksheet count seems to be rather limitless

Thanks
-marc

Re: Temporary File Is Lost

All -

I was able to resolve, but not by searching the HD.
The temp file was stored in this location

C:\Documents and Settings\SnydeW01\Local Settings\Temporary Internet Files\Olk17\.....
But when I try to navigate the path, the Olk17 folder cannot be found
I can only guess that since I am not admin on the laptop that Ido not have rights

To work around, I opened another excel attachment in outlook
I then clicked open and the Olk17 was the default path

Opened my file and resaved somewhere that I could use it.
Phew!!!!!!

Thanks
-marc