Posts by Fencliff

Re: Format Border Based On Cell Content

Dave, at least in Excel 2003 you can't (oddly enough) apply medium width borders with conditional formatting. It's one of these Excel "features" I've never really understood myself.

Re: Copy Status Bar Stat To Clipboard

Quote from mhabib

One last question: Is there a way to programmatically include the MS Forms 2.0 Reference?

This is what I use. I am not actually sure whether the GUID number is the same from Excel version to another.

sFormsGUID = "{0D452EE1-E08F-101A-852E-02608C4D0BB4}"
 ThisWorkbook.VBProject.References.AddFromGuid GUID:=sFormsGUID, Major:=1, Minor:=0

I know I have the code to find out the GUID name for all the references in a project on my work computer, I can post it tomorrow if this does not work.

Rgds,
Jani

Re: Tabbing Through Worksheets

Yes, Ctrl + PgDown to next sheet and Ctrl + PgUp for previous.

Rgds,
Fencliff

Re: Goto Command?

I think I figured out your problem. You have declared vCriteria6 as Boolean, although the answer is a actually numeric, vbYes and vbNo are constants that correspond to an integer (or long?). Try declaring the variable as Long instead of Boolean, should work.

Hope this helps,
Jani

Re: Copy Status Bar Stat To Clipboard

You need to reference the Microsoft Forms Object Library. Simply in VBE go to Tools > References... > Microsoft Forms 2.0 Object Library and Bob's your uncle.

Re: Goto Command?

Hi,

If you haven't done so, you need to define the line number you want to refer to in the beginning of the line like this:

If vCriteria = vbYes Then Goto 39


'...code...


39 'Jump here if vCriteria returns vbYes

Or for Line label:

If vCriteria = vbYes Then Goto  myLabel


'...code...


myLabel:
'Jump here if vCriteria returns vbYes

Personally, though, I don't like using GoTo too much, as it makes following the code and controlling your variables really hard in the end of the day. If you can give a little more specific description of what you are trying to do, perhaps I can help.

Rgds,
Jani

Re: Border Formatting Based On Cell Content

Instead of

With .EntireRow.Borders(xlEdgeBottom)

use

With .Resize(1, 10).Borders(xlEdgeBottom)

Where 10 is the number of columns you want to extend the border to.

Rgds,
Fencliff

Re: Border Formatting Based On Cell Content

Here is a macro that will do this from Cell A1 to the last filled cell in column A. If you need this to run constantly, you can use the Worksheet_Change event to monitor the column.

Sub DrawBorders()


    Dim rCell As Range
    Dim rRange As Range
    
    Set rRange = Range("A1", Range("A65536").End(xlUp))
    
    For Each rCell In rRange
        If Not IsEmpty(rCell) And _
           Not IsEmpty(rCell.Offset(1, 0)) Then
            With rCell
                If .Value <> .Offset(1, 0).Value Then
                    With .EntireRow.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                        .ColorIndex = xlAutomatic
                    End With
                End If
            End With
        End If
    Next rCell


End Sub

Hope this helps,
Fencliff

Hi all,

I am fighting a quixotic battle against a problem, that I realise may well be unsolvable. I have a worksheet that consists of approximately 27,000 rows in a four-level structure, like so:

1
1.1
1.1.1
1.1.1.1

To make the sheet easier to navigate I have created an expanding/collapsing tree structure by using simple hide and unhide rows, and on the last level of headings (1.1.1) I have cells that change between "+" and "-" depending on whether the fourth-level subset is currently visible or hidden. Naturally I need to keep the cell as "+" when the set is collapsed, and "-" when expanded. I have accounted for all methods user can change the hidden settings of those rows, but one:

When users autofilter the list, the +/- signs obviously screw up, because filtering resets the user-set hidden-settings. Suddenly I have expanded subsets with "-" on headings that were previously "+".

Now this is obviously a vanity problem, as the +/- sign is not integral to the working of this macro, but the problem is that with three different levels of headers the worksheets starts to look a little cluttered and these signs really help.

Finally, my question: Is there any way to trigger a procedure to reset these signs upon/after autofilter? I realize that this is not a built-in Excel event, but a workaround will do just fine. Any way to detect that an autofiltering has taken place without having to check the .Filtermode every second?

Any help would be greatly appreciated. If you think that an example of the code/workbook might help, let me know, and I'll try to strip a small example out of this monster of a spreadsheet.