Posts by Fencliff

Re: Or Operator

Unfortunately you cannot use the OR operator in this scenario. OR is a logical operator, and can be used to test the validity of at least one statement out of many. For Example

If x = 1 Or x = 10 Or x = 20 Then
    'If x is 1, 10 or 20, the logical test evaluates to true
End If

Regarding how you are initially using the SumIf function, I am sure someone will suggest a DSUPERSUMIF formula to do this, but there is nothing wrong with your initial syntax. If you want to make it more readable, you could do for instance:

Dim rngIf   As Range
    Dim rngSum  As Range


    Set rngIf = Sheet5.Range("H9:H500")
    Set rngSum = Sheet5.Range("I9:I500")
    
    With WorksheetFunction
        Cobbs = .SumIf(rngIf, "C/F", rngSum) + .SumIf(rngIf, "4/F", rngSum)
    End With

Re: Run Macro

Hi,

Put the following code into the sheet module of the sheet you are using, and change the YourMacroNameHere to whatever your macro is called:

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Address = Me.Range("A1").Address Then
        If Not IsEmpty(Target) Then
            Call YourMacroNameHere
        End If
    End If


End Sub

Re: Pass Element Of User-Defined Data Type

The problem here is your architecture. Think of user-defined type as an object (UserType) that has properties (Element1, Element2). The objects are made from the template that is your UDT, but the objects created from that template don't bear any relation to each other.

To speed up writing the information back into a sheet, what you could do without having to rewrite your entire logic is to loop through your array "Array", writing each Element1's (or Element2's) property into a variant array, which will enable you to write it all into the sheet at once.

It'll be far from efficient, but better than looping the values directly into the sheet. If you are open to a complete rewrite, I can give you some pointers how to actually make a sensible infrastructure.

Re: Stop Macro Running After Specfied Time Period

Out of curiosity, could you describe a little what your macros do? It seems a tad outlandish that any Excel based macro should run for 54 hours, or to be more exact, that any test that could run for 54 hours would be done in Excel (I've done GUI hittests that have been running for a week in a streak, or batch migrations that can execute code top-down for a couple of days, but in those cases I'm damn sure not doing it in VBA!)

I am assuming that your application loops through some calculation or data. You could simply record the time at the the start of the calculation, and on each running of the loop test whether the maximum time has elapsed, and exit if it has.

Re: Pass Element Of User-Defined Data Type

Hi,

Could you please explain what your application is trying to achieve and what is the design decision behind using arrays of UDT's. It seems to me that this could be achieved much better by a collection of UDT's or a class object.

Re: Freeze Shape From Scrolling

The reason why Excel doesn't allow floatings shapes is not because of MSFT's oversight. If you think about it, you soon will realize the following:

1 Excel is a spreadsheet application
2 Spreadsheets are about manipulating data
3 When you have a floating object over a data grid, it will block the user's view of the data
4 This is a bad idea

Apart from that, the commandbar/toolbar approach has multiple advantages

5 The contols are designed for this exact purpose. You can have textboxes, combo boxes, and buttons just you would on the sheet, but in addition dropdown menus, multi-level hierarchies etc.
6 Your controls aren't tied to the sheet. You don't have to replicate your navigation elements on each sheet you want to use them on, and you don't (necessarily) have to proliferate your code either, which increases it's maintainability.
7 Your users can use an interface already familiar to them, enhancing the user-adaptability or your application
8 Uniformity to Excel's native UI gives your application a more professional feel - you can use the hundreds of pre-existing button icons, or make your own. The buttons respond to user hovering over and clicking on them, resulting in immediate feedback, and an application that seems more responsive.

And no, you don't want to look into the winapi solution. It will be messy, resource expensive, and involve hella-complicated code (compared to vanilla VBA, at least).

Re: Freeze Shape From Scrolling

Quote from CATman

Maybe I should open up this question by asking, "What is a good method to provide a user with buttons that can be used to launch various macro's" besides using hot keys.

The question posed that way is much easier to answer: A custom toolbar. Check out http://www.ozgrid.com/VBA/toolbar-remove-restore.htm and google around with words Excel VBA Toolbar and specify your searches as needed to find out how.

Re: Freeze Shape From Scrolling

Like AAE said, this is not really possible. A hack could be made with windows API's to change the location of the shape relative to the visible portion of the screen, but that is really not anything you would want to do.

In your place I would consider using a toolbar menu with a single button. It can be set to float on top of your sheet just like you would want your shape to do, and if you don't want the user to move it around, that can be done using the Protection property. It won't look the same, but will fulfill the exact same function as a shape.

Re: Extract Dimensions From Single Cell Entry

Quote from p45cal

This line

Code

strVal = Replace(strVal, ".", ",")

caused a few problems in the English locale of Excel on my machine. (Or is that what makes it the real man's way!?:smile: )
p45cal

Ah, well, the real man does what real man wants and screw the 80% majority or users, the most important thing is that it works for me!

Yeah, should probably think a little more when posting sometimes. Usually when the case is that I (or the OP in this case) needs something done as a one-time event, i.e. format garbage data, I just write the code out in two minutes and don't really bother to harness it with a multi-locale test scenarios

Thanks for pointing that out though.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Quote from AAE

p45cal - nice work. Do you by chance have a handy reference to the character codes? I'm looking for one.

The Chr function codes are really just the ASCII numbers for characters, 0 to 255.

The real man (again) writes this to Immediate window in VBE:

Workbooks.Add: For i = 0 To 255: Range("A" & i + 1).Value = i: Range("B" & i + 1).Value = Chr(i):Next i

... and he remembers the meanings of 9 and 13 by heart, because they're illegible in that output. The rest can refer to for instance:

http://www.petefreitag.com/cheatsheets/ascii-codes/

Re: Extract Dimensions From Single Cell Entry

I can see this question has already been answered, but to post what I already wrote, if you want to do this the real man's way with a quick and dirty, nested loop procedure, then here you go:

Sub GetNumbersFromSelection()


    Dim rngCell         As Range
    Dim rngTarget       As Range
    Dim strVal          As String
    Dim astrSplit()     As String
    Dim i As Long
    
    For Each rngCell In Selection
        If Not IsEmpty(rngCell) Then
            strVal = rngCell.Value
            strVal = Replace(strVal, """", "")
            strVal = Replace(strVal, ".", ",")
            strVal = Replace(strVal, " ", "")
            
            astrSplit = Split(strVal, "x")
            
            Set rngTarget = rngCell.Offset(0, 1)
        
            For i = LBound(astrSplit) To UBound(astrSplit)
                rngTarget.Offset(0, i).Value = CDbl(astrSplit(i))
            Next i
        End If
    Next rngCell
    
End Sub

Right click the sheet containing your data, choose View code, and paste the above into the opened window. Then select the cells where your measuments are (in one column), and press Alt + F8 and run the macro GetNumbersFromSelection.

Re: Delete First 3 Characters In A String

AAE got the right idea, but to make it a little more reliable, I would use

=RIGHT(A1, LEN(A1)-3)

This doesn't assume that the string is 6 characters long, but always rips out the first three characters.

Re: Freeze Panes VBA Without Select

Dave,

Could you please post a small example how you would, using VisibleRange and GoTo, achieve the following, when used in conjuction with worksheet_activate event:

• Freeze panes on a certain cell, that may or may not be in view when the sheet is activated
• Ensure that the scroll position where the user left it when deactivating the sheet is preserved
• Ensure that the cells that user had selected before deactivating the sheet are kept selected
• Do this without a displaying a distracting twitch

I think the list above is a reasonable set of demands in good UI design.

Also, could you please try to explain why is it better to set the window's freezepanes status to the same point every time the user navigates to the sheet, when you could only do it once when you are running other code (whatever OP is running).

I would really appreciate it, because I just don't get why your way is better than mine.

Re: Get Data From Closed Workbooks

Hi,

Attached below is a version of the code that works. Please take some time to study the differences to understand what made it fail in the first place.

Because I am obsessive compulsive when it comes to these things, I changed the variable names to include the data type prefix, declared your arguments as strings, and shuffled some other things around as well. Those were not the reason why the code didn't work though. The culprit were the lines:

'Create the argument
    arg = "'" & path & "[" & "k217811.xls" & "]" & "Total" & "'!" & _ 
     Range(ref).Range("A1").Address(, , xlR8C2) 
     '   Execute an XLM macro
    getvalue = ExecuteExcel4Macro("arg")

And the enhanced version:

Private Function GetValue( _
    ByVal strPath As String, ByVal strFile As String, _
    ByVal strSheet As String, ByVal strRef As String) As Variant
     
     'Retrieves a value from a closed workbook
    Dim strArg As String
     
    'Make sure the strFile exists
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    
    If Len(Dir(strPath & strFile)) = 0 Then
        Exit Function
    End If
     
    'Create the argument
    strArg = "'" & strPath & "[" & strFile & "]" & strSheet & "'!" & _
        Range(strRef).Cells(1).Address(ReferenceStyle:=xlR1C1)
     
    'Execute an XLM macro
    GetValue = ExecuteExcel4Macro(strArg)
     
End Function

Re: Freeze Panes VBA Without Select

Dave,

I know my code uses select and activate, because that's the only way to do it. The code I posted was simply showing how I've chosen to work with the problem. If there is a better way, I would be very glad to learn, becaused I have a couple of apps out there that are using this method, and I don't feel very confident in it.

The question however is completely sensible. You start programming in VBA, and you learn that you don't need to select cells to do things, and suddenly you can't figure out how the heck to freeze panes at a certain point without selecting. No false premises there, IMO.

Hooking the freezepanes into the worksheet_activate event, while relatively reliable, would be _really_ annoying, because the screen would twich every time you activate a sheet. In the best case scenario, where you would record the previous selection, scroll status, and then toggle them back, the screen would still twitch every time you activate a sheet. With a lot of data, and especially with some shapes (such as charts) mixed in, this can look really bad, as the redrawing time can be perceivable. Or if the user has selected the entire sheet or other large amount of data, and changes sheets temporarily and goes back. On my computer selecting large filled ranges can sometimes freeze Excel for 3-10 seconds.

Or if the user doesn't like the freezepanes and turns them off, then goes to check another sheet and coming back, bam! The old settings are back. Not to mention the fact that it is completely unnecessary to redo this at every sheet activation, once you freeze the panes while you are running other code anyway, like OP, you don't need to mess with it again until the next time you turn them off.

The event handler I tried to test this looks like this:

Private Sub Worksheet_Activate()


    Dim rngSelection          As Range
    Dim lngScrollRow          As Long
    Dim lngScrollCol          As Long
    
    Set rngSelection = Selection
    
    lngScrollRow = ActiveWindow.ScrollRow
    lngScrollCol = ActiveWindow.ScrollColumn
    
    If ActiveWindow.FreezePanes Then
        ActiveWindow.FreezePanes = False
    End If
        
    Range("C6").Select
    
    ActiveWindow.FreezePanes = True
    
    rngSelection.Select
    
    If ActiveWindow.ScrollRow <> lngScrollRow Then
        ActiveWindow.ScrollRow = lngScrollRow
    End If
    
    If ActiveWindow.ScrollColumn <> lngScrollCol Then
        ActiveWindow.ScrollColumn = lngScrollCol
    End If
    
End Sub

Peruse it at your will, but you should know that it needs some failguards for if the selection is not a range.

Re: Create Object In Vba

I'm not sure if this does exactly what you want, because I'm not clear on what you mean by binding the object to a cell. This should however insert an object to cell A1, the trick is to activate the cell before inserting the object, because the objects always get inserted to the active cell.

I made a couple of little changes also, please read the comments in the code for details. However, by the end of this code, you will have reference to the shape containing the object, and the object itself, both of which you can use in which ever way you want to proceed.

Sub AddObjectToA1()


    Dim lngShapeCount   As Long
    Dim wksSheet        As Worksheet
    
    Dim shpContainer    As Shape
    Dim objObject       As Object
    
'   Get reference to the sheet where we want to add the object
    Set wksSheet = ActiveSheet


'   Get the count of Shapes on on the sheet
    lngShapeCount = wksSheet.Shapes.Count
    
'   Excel adds the object to the active cell, so activate cell A1
    wksSheet.Range("A1").Activate
    
'   Open the dialog, using the "proper" way to do it. The dialogs collection is
'   always available, but the commandbar control used before might not be accessible
'   if the user has customized his menu
    Application.Dialogs(xlDialogInsertObject).Show
    
'   Compare the number of shapes on the sheet before and after showing the dialog.
'   If the count has been incremented by one, we know that our object was added
    If wksSheet.Shapes.Count = lngShapeCount + 1 Then
    
        'This sets reference to the shape that contains the Object
        Set shpContainer = wksSheet.Shapes(wksSheet.Shapes.Count)
        
        'This sets reference to the OLEObject itself
        Set objObject = shpContainer.OLEFormat.Object
        
    Else
    
        'The user cancelled or there was an error, so we exit
        Exit Sub
        
    End If
    


End Sub

Re: Assign Variable To Different Data Types

Hi,

I'm not sure why your excel locks up at that point, is should work all right. However, you could try this:

Cells(1, CLng(ColumnUsed)).EntireColumn.Copy

To replace all of this:

Cells(1, ColumnUsed).Select 
    Cells(1, ColumnUsed).End(xlDown).Columns.EntireColumn.Select 
    Selection.Copy

The CLng function converts the variant into a long integer, ensuring it's passed to Cells as number. For my system though, I can easily pass a variant to Cells without crashing, heck, I can even pass a string, apparently!

And if that copy is followed by a paste (i.e. you don't access the data on the clipboard or paste into another application, instead of copying the contents to clipboard, then pasting and clearing the clipboard you could do the following to for example copy the column into the column A in Sheet2:

Cells(1, CLng(ColumnUsed)).EntireColumn.Copy Destination:=Sheet2.Range("A1")

And one more thing, you don't need that 26-case select case statement. You can replace the whole shebang with:

ColumnUsed = Columns(ColumnUsed).Column

Good luck with your project!

Re: Freeze Panes VBA Without Select

I think everybody in this thread have temporarily lost their ability to read.

The OP's question was simple: Whether it is necessary to select a cell or cells (you know, using Range.Select or Application.Goto or whatever else has been suggested in this thread) before applying freezepanes.

The OP then proceeded to acknowledge the correct answer, which is that because FreezePanes is a method of an window object, the sheet on which you want to apply it needs to be the activesheet, and it is always applied to the top and left of the selected cells.

The OP didn't ask to freeze panes every time the worksheet is activated.

But to actually contibute, this is how I'm doing it now:

Sub Foo()


    Dim rngPrevSelection As Range
    
    'On your calendar sheet...
    Set rngPrevSelection = Selection
    
    ActiveWindow.FreezePanes = False
    Application.ScreenUpdating = False
    
    'Do your thing on the other sheet
    wksOtherSheet.Activate
    
    '...
    '...
    
    'Return to your calendar sheet...
    wksCalendarSheet.Activate
    
    Application.ScreenUpdating = False
    
    wksCalendarSheet.Range("B3").Select
    ActiveWindow.FreezePanes = True
    
    rngPrevSelection.Select

I use the rngPrevSelection to store the previous selection and then switch it back, because that's just good practice. Please note however that if the selection is not a range (a chart, for instance) this will return a Type Mismatch error. However, if I remember correctly if you try to turn FreezePanes = False and you have a chart selected, it will fail anyway.

Re: Disable Hyperlink From Opening Until Confirmed

Here's a little more fleshed out version of what I posted above, figured one day this might come in handy. This would be fairly easy to modify to for example validate user input before using a POST on a web page through a hyperlink.

I haven't used Excel's hyperlinks that much before, so I'm sure there's some horrible bug I don't know about. Please let me know if you find any, as this is going to my code library at work.

Anyway, what you need to do is the set the hyperlink to refer to the cell it is placed in, and use the ScreenTip property of the Hyperlink object to carry the address you really want to go to. You can either specify an external file, a location within the same workbook, or a link to an external workbook AND a location within it. The latter is achieve by separating the file reference and the location by an asterisk, like: "C:\Workbook.xls * MySheet!A1:A10". Any blank space between the asterisk and the parameters doesn't make a difference.

The ScreenTip property can be changed by going to Edit Hyperlink... > ScreenTip... or directly in the code like ActiveCell.Hyperlinks(1).ScreenTip = "..."

For more detailed explanation, see the code comments, which I have abused as per usual.

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)


'   Event Handler  :  Workbook_SheetFollowHyperlink
'   Purpose        :  Asks for user confirmation before following a hyperlink
'   Notes          :  The hyperlinks you want to have confirmed need to have an empty
'                     Address property and a SubAddress property that refers back to
'                     the cell where the hyperlink is located. The ScreenTip property
'                     must either contain a reference to a file, reference to a range
'                     in the same workbook, or both, delimited by an asterisk (*)


'   Because we will be disabling our events, we need to make sure that if an error occurs
'   the events are turned back on
    On Error GoTo EventExit
    
    Dim strThisCellRef  As String
    Dim strThisCellAddr As String
    Dim strParam        As String
    Dim strAddress      As String
    Dim strSubAddress   As String
    Dim lngDivPos       As Long


'   If the hyperlink doesn't have a specified ScreenTip property, we exit
    If Len(Target.ScreenTip) = 0 Then Exit Sub


'   If the hyperlink has an address property, this is a "true hyperlink", so we exit
    If Len(Target.Address) Then Exit Sub


'   Disable application events, so we don't end up in a loop when following hyperlinks
    Application.EnableEvents = False
    
'   Get the address of the cell containing the hyperlink without dollar signs
    strThisCellAddr = Replace(Target.Range.Address, "$", vbNullString)
    
'   Compose a string in the format SheetName!Address
    strThisCellRef = Sh.Name & "!" & strThisCellAddr
    
'   If the subaddress property of the hyperlink is equal to the current cell's address,
'   we know that the hyperlink is pointing to itself, so we can tell for sure that this
'   hyperlink is supposed to be caught by our event handler
    If Target.SubAddress = strThisCellAddr Or Target.SubAddress = strThisCellRef Then


'       If the user clicked Yes
        If MsgBox("Follow hyperlink?", vbYesNo + vbQuestion) = vbYes Then
        
'           Get the ScreenTip property into a string variable to avoid having to resolve
'           the property when examining it
            strParam = Target.ScreenTip
            
'           If the ScreenTip text contains an asterisk, we know that it includes
'           both Address and SubAddress properties. Asterisk is convenient, because
'           standard filenames, web addresses, sheet names or range names cannot have it
            If InStr(1, strParam, "*") Then
            
'               Get the position of the asterisk
                lngDivPos = InStr(1, strParam, "*")
                
'               Get the characters from the left of asterisk to be our Address property
'               The trim function is used so we can have white space between the asterisk
'               and the properties around it to make the screentip more legible
                strAddress = Trim$(Left$(strParam, lngDivPos - 1))
                
'               Get the characters from the right of the asterisk to be our SubAddress
                strSubAddress = Trim$(Right(strParam, Len(strParam) - lngDivPos))
                
'               Follow the hyperlink using both of the above
                ThisWorkbook.FollowHyperlink strAddress, strSubAddress
                
                
'           If the ScreenTip text contains chars "\" or "/" we know that it cannot be a
'           cell address, because sheet or range names cannot contain these characters
            ElseIf InStr(1, strParam, "\") Or InStr(1, strParam, "/") Then
                        
'               Follow the hyperlink using the ScreenTip as our Address
                ThisWorkbook.FollowHyperlink Address:=strParam
            
'           If the ScreenTip didn't contain asterisks or slashes, we know it is a
'           reference to a range
            Else
      
'               Follow the hyperlink using the ScreenTip as our SubAddress, and because
'               the Address was not specified, we know it refers to ThisWorkbook
                ThisWorkbook.FollowHyperlink ThisWorkbook.Name, strParam


            End If
        End If
    End If
    
EventExit:


'   When exiting the procedure, turn application event listener back on
    Application.EnableEvents = True
    
End Sub

Re: Disable Hyperlink From Opening Until Confirmed

How about using the cell's own address as the hyperlink address like Dirk suggested, and load the actual address you want to follow to the hyperlink's ScreenTip property. Then you could simply do

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)


    On Error GoTo ErrorExit
    
    If MsgBox("Follow hyperlink?", vbYesNo + vbQuestion) = vbYes Then
    
        Application.EnableEvents = False
        ThisWorkbook.FollowHyperlink Target.ScreenTip
        Application.EnableEvents = True
    End If
    
    Exit Sub


ErrorExit:
    Application.EnableEvents = True
    
End Sub

It is a good idea to let your user know where he or she is heading anyway, so the screentip will also fulfill that function.

Of course as is, this would only work with simple links to external documents, but for in-document hyperlinks you could simply check if the string has character "!" and whether the text to the left of it is a valid sheet name in your workbook, and then use it as a SubAddress property instead.

Perhaps I'll come back to this later if I have the time and try to make a catch-all solution.

Re: No More New Fonts May Be Applied In This Workbook

Check out this KB article for a fix:

http://support.microsoft.com/d…spx?scid=kb;EN-US;Q215573

But if I were you, I would look into the very root cause of the problem, namely the fact that your workbook has 33 sheets and over 150 charts! Could it be perhaps broken into more sensible pieces?