Posts by Aaron Blood

Re: Copy Data From Several Sheets Into One Worksheet

I would've suggested:
Change this:

Cells(1, 1).CurrentRegion.Select

To this:

Range("A21").CurrentRegion.Select

But it's possible there may be extra data above that gets picked up.
Glad you got it working...

Re: Copy Data From Several Sheets Into One Worksheet

Change this:
Cells(1, 1).CurrentRegion.Select

To this:
Range("A21:O21").Select

Re: copy date from several sheets into one sheet

Would you believe someone asked me the same question... 11 years ago.

Here's an example:
consol_loop.zip

Ran across this problem again the other day:

How do you determine if a font should be black or white based on the background color of the cell?

After a little searching, I managed to find a cool (and simple) VB algorithm.

Sub SetFontColor()
    Dim cell As Range
    For Each cell In Selection
        cell.Font.Color = BorW(cell.Interior.Color)
    Next cell
End Sub


Function BorW(RGB As Long) As Long
    Dim R As Integer, G As Integer, B As Integer
    R = (RGB And &HFF)
    G = (RGB And &HFF00&) / 256
    B = (RGB And &HFF0000) / 65536
    BorW = vbWhite
    If R * 0.3 + G * 0.59 + B * 0.11 > 128 Then BorW = vbBlack
End Function

The Luminance Formula:
R * 0.3 + G * 0.59 + B * 0.11 = L

This is the formula for Luminance, or the brightness of a color.
It is most commonly used to convert colors to grayscale.

Re: Migration after 3 years?

Quote from Dave Hawley

My pet peeve is the number of rows M$ supplies. Heck, if you need 1/4 of the total rows you are using the wrong tool for the job.

I've got a few workbooks with sheets that have maybe 8k rows of monthly data. Of course, then I have to consolidate the monthly data to annual with a few dozen sumif/sumproduct type formulas and that causes like a 30 second recalc delay between button pushes.

Can't imagine even coming close to actually using the 65k rows the previous incarnation allowed... I can only say that it is logical in a spreadsheet app to have more than 365 columns. I think double the 256 cols and a quarter of the 65536 rows would've been perfectly fine for a spreadsheet app.

Of course, we can't blame it all on Excel... they were just stealing... um... I mean... allowing compatibility, to predecessor apps.

Re: What do you think of...

Sure, if you like...

Re: Migration after 3 years?

Quote from florachristi

2007 package is advance of 2003 its nice to use and makes very easy to work also,,,,,,

You can't even edit a formula and use the scroll wheel. If you haven't noticed that, then:

1) You still have the redundant "Edit Directly in Cell Option" checked.

2) You don't do much edit/auditing of formulas.

What's bad about 2007...

Generally, the more advanced the user, the more they hate the Excel 2007 UI.

There are zero options for intermediate users to quickly create custom toolbars. This one doesn't bother me so much because I know how to manipulate the complex code required to add my own toolbar features (a complicated dance between VBA and XML that will baffle the majority of users). I've alredy seen that this has been improved upon a bit with Office 2010. Perhaps they'll also add/allow additional ribbon docking on the right-left-bottom-float of the work area.

There are significant holes in the code/design that still will aggrevate even the most advanced developers (calling custom png button images at runtime for instance).

Certain time-saving features (like dragging ranges onto charts to add new series) have been removed.

I'm hopeful that in Office 2010 they'll at least fix the formula edit scroll wheel problem.

So what's actually improved?

They added five new formulas and made the Analysis Toolpack part of the standard native functions. Out of the 5 new functions, only 1 of them is of any real interest (IFERROR) and addresses one of my previous Excel pet peeves. I was happy to see it included.

They got rid of the retarded paperclip (nuf said).

They increased the number of cells; although this one is debatable as an actual improvement since certain VBA properties were not designed to return cell counts in the range of doubles so they had to add new CountLarge methods to handle the bloated cell counts of worksheet objects.

Support for dual processor calculations... I think this is an improvement, but haven't actually done any testing on the calc-speed having it enabled-disabled.

The Name Manager was improved (at least somewhat). You can delete more than one name at a time. Workbook names that are hidden are still just like viruses since there is still no way for the intermediate user to review the hidden names that are lurking within worksheets (or delete them). I still have to use my own Name Manager add-in to really control the viral hidden workbook names.

On the one hand, the ribbon is flexible enough that you can actually recreate a familiar Excel 2003 interface in the ribbon environment as a custom tab (although you do really have to be amongst the Excel power user ranks to figure it out). On the other hand, why didn't MS just include a Classic ribbon tab so I didn't have to waste two weeks of my life re-building it? They still offer Lotus 1-2-3 transition keys, but nothing for the Excel 2003 users?

Re: What do you think of...

Quote from Dave Hawley

Why?

Alright, a little background...

If you want to call a macro from the ribbon, the syntax of the macro must be:

Sub MyMacro(control As IRibbonControl)

Above is the typical code example provided online, in books and so forth...

I was converting a bunch of my old macros to use with the ribbon and I was just adding the above control syntax. I ran into a problem when some of my macros were making calls to their subroutine buddies that now had this control variable requirement.

There are 3 ways you can solve that problem:
1) make the IRibbonControl optional, or...
2) dimension a DummyRibbon as an IRibbonControl and pass it
3) create a seperate sub with the IRibbonControl variable that makes the call to MyMacro without the control requirement

The first option above obviously makes more sense because it requires no additional coding in subs that call MyMacro and it also works fine if you happen to assign macros to shape click events (as I often do).

So, if I want to allow my macros to be called directly or from the ribbon it seems to make sense to always use the following "Optional" syntax.

Sub MyMacro(Optional control As IRibbonControl)

Now, if I want to write my macros so I can run the code in Excel 2003 or 2007 I have a new problem; the IRibbonControl did not exist in Excel 2003. As it turns out, the Ribbon doesn't necessarily care if it's allowed to pass along itself to a routine that is setup to receive a Variant as opposed to an IRibbonControl, and we know that Excel 2003 allows Variants. So now I have this final syntax that allows for direct and ribbon calling in Excel 2007, and will also work if someone tries to run the code in Excel 2003.

Sub MyMacro(Optional control As Variant)

I saw Ger Plante's suggestion to change the name of the variable from "control" to "variable". Obviously, it can be named anything you want. I'll probably leave mine as "control" just because it reminds me of the origin of why I included it. I'm also hopeful that when people see it in my signature, the "control" reference might help them to make the lateral connection.

Re: What do you think of...

That's what makes it a good signature...

It sorta reads like a joke, but actually, I'm serious. That is (literally) the format that I'm going to use for all macros.

It does actually make sense.

...my signature?

Seems like this is the standard syntax people should be using for all macros now.

Optional - allows ribbon callbacks or direct referencing
Variant - allows 2003 backward compat

Re: Limit Number Of Characters In Autoshape

You say you have a diamond shape added by a macro?

1) When you add that diamond shape you can assign a macro to it's click event.

2) The macro assigned to the diamond(s) displays an inputbox with a specified character limit.

3) The inputbox persists until the user makes an appropriate entry.

Option Explicit


'Example code to add a diamond shape
Sub AddDiamondShape()
    Dim ShapeName As String
    With ActiveSheet.Shapes.AddShape(msoShapeDiamond, 234.75, 42.75, 165.75, 144#)
        ShapeName = "Diamond" & ActiveSheet.Shapes.Count
        .Name = ShapeName
        .OnAction = "DiamondInput"
    End With
End Sub


'Code assigned to the click-event of each diamond
Sub DiamondInput()
    Dim CurText As String, InputText As String, Msg As String
    Dim Limit As Integer
    Dim Diamond As Shape
    
    Limit = 15
    Set Diamond = ActiveSheet.Shapes(Application.Caller)
    CurText = ""
    
    On Error Resume Next
        CurText = Diamond.TextFrame.Characters.text
    On Error GoTo 0
    
    Do
        Msg = "Enter Text" & vbCrLf & "Max Characters = " & Limit & vbCrLf & "Chars: " & Len(CurText)
        InputText = InputBox(Msg, "Diamond Shape Text", CurText)
        CurText = InputText
    Loop Until Len(InputText) <= Limit
    
    With Diamond.TextFrame
        .Characters.text = InputText
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
    End With
    
End Sub

You can do something more elegant with a userform instead of a simple inputbox, but this will probably suffice.

Re: Two Next Statements

I think...

For z = 101 To 200 
     i=i+1
     
Next z

...or: i =z-100

Just not sure if I'm following that explanation. I think you just want a counter (i=i+1) in there for the i variable?

Re: Migration after 3 years?

I'm at least beginning to make some effort...

My company has not adopted it yet, although I'm told they have begun some "pilot programs". Amazing to me, 3 years and we're just now starting pilot programs. Amazing in the sense that it shows just how bad the UI is.

I now have an installation of Office 2007 on a seperate laptop in my office. After a week of toil, I've managed to rebuild (almost) the entire Excel 2003 Classic Menu as a tab in the ribbon. Not sure if I should bother to make it available to folks as an addin or if it's old-hack at this point.

As we approach 2010...

Has everyone finally made the transition to Office 2007?

...or does a large majority of the business world still despises the ribbon enough to resist the urge?

Re: 2009 Daytona 500 Pics

Yeah there was a lot of aggressive driving and about 5 or 6 crash induced cautions. The big crash was on the approach to turn 3 and involved Dale Jr. and Vickers; took 9 cars out of the running.

I'll probably be attending at least one or two more races later this year down south at the Miami Speedway. Maybe we'll have better weather next time...

For those who are unaware, Feb 15th marked the 51st running of the Daytona 500 here in Florida.

My family and I attended the event. Was kind of an anti-climactic ending being red-flagged after 152 laps... but we had a good time anyway.
[Blocked Image: http://www.xl-logic.com/pics/hobby/Daytona/Daytona_01.JPG]

View of the main grandstands from our seats on the backstretch.
[Blocked Image: http://www.xl-logic.com/pics/hobby/Daytona/Daytona_02.JPG]

F-15 flyover before start
[Blocked Image: http://www.xl-logic.com/pics/hobby/Daytona/Daytona_04.JPG]

The Chevy Camaro pace car was driven by Tom Cruise
[Blocked Image: http://www.xl-logic.com/pics/hobby/Daytona/Daytona_05.JPG]

Pace lap coming around turn 2
[Blocked Image: http://www.xl-logic.com/pics/hobby/Daytona/Daytona_07.JPG]

My son posing with cars rounding turn 2 and heading down the back "superstretch"
[Blocked Image: http://www.xl-logic.com/pics/hobby/Daytona/Daytona_08.JPG]

Daytona is a 2.5 mile track so they do 200 laps for the 500 mile race. Most people don't have an appreciation for just how big the track is when they only see it on TV.
[Blocked Image: http://www.xl-logic.com/pics/hobby/Daytona/Daytona_09.JPG]

The back superstretch with turn 3 and 4 in the background
[Blocked Image: http://www.xl-logic.com/pics/hobby/Daytona/Daytona_10.JPG]

moving slow under caution
[Blocked Image: http://www.xl-logic.com/pics/hobby/Daytona/Daytona_11.JPG]

Re: Custom Toolbars And Faceids

I like it!

Re: Conditional Fill-Down Until Next Blank Cell

For the example you provided, it appears as though column "F" is the key column for the filldown.

In which case, you could do something like this:

Sub Fill_empty_cells()


Sub Fill_empty_cells()


    Dim KeyCol As Range, cell As Range
    Dim CurReg As Range, FillCols As Range
    Dim HdrRows As Integer
    
    HdrRows = 2
    Set KeyCol = Range("F:F")
    Set FillCols = Range("B:E, G:H")
    
    Set CurReg = Range("A1").CurrentRegion
    Set CurReg = CurReg.Resize(CurReg.Rows.Count - HdrRows).Offset(HdrRows)
    Set KeyCol = Intersect(KeyCol, CurReg).SpecialCells(xlCellTypeBlanks)
   
    For Each cell In KeyCol
        Intersect(Range(cell, cell.End(xlDown).End(xlDown)).EntireRow, FillCols).FillDown
    Next cell
   
End Sub




    
End Sub

A next step might involve deleteing all the rows that have blank cells in column "F" in which case you could just add this next line to the bottom...

KeyCol.EntireRow.Delete

Re: Application.Goto Trick

I didn't really want to get too far into discussing the app I'm working on so much as the concept... but it's a good question.

It's a WB name manager worksheet, I dump the names in there, make a bunch of changes to em, sometimes I want to navigate to the changed reference before I actually assign it to the name.

This just happened to be one situation where the issue cropped up again. I've had other situations where I've needed to navigate to a string range reference in this sheet!range format. Seems to me there are some objects that I've come across that return string addresses in this format...

Oh and I did test your last suggestion:

GotoAddr = "Apr!$L$72:$O$76"
Application.Goto Range(GotoAddr)

As I suspected, it doesn't work (maybe they tweaked it a bit in 2007). Too bad, it would've been cleaner.
That suggests to me that in 2007 you might be able to just use:

GotoAddr = "Apr!$L$72:$O$76"
Range(GotoAddr).Select

Is that true?

Re: Application.Goto Trick

Yeah, it's not really a situation where range names are handy... I'm processing like several hundred string references. Actually, it's working from a dump of defined workbook names (insert|name|paste|pastelist) and it provides all the refersto addresses in A1 string format. I wanted to d-click a cell to follow the link, so I do stuff like this in the event procedure of the sheet so I can see where the names are.

I haven't tried that last combination you suggested though. I'll maybe give that one a shot tomorrow. Seems like it shouldn't work... but who am I to say what's logical in VBA.