Posts by Phil

  • Calulating just visible cells of an Advanced Filter area.

    I’m sure it’s simple but I’m stuck and see the solution. I’m running a pretty large list of inventory items. At the top of list I’m counting the unique items based on criteria (Crates, Cases, Pallets, etc.). Then I’m totaling the cubic footage of those various categories of items. Next I’m totaling the weight of the same.


    I’m doing all this with formulas, such as…
    =COUNTIF(SU,"Crate")
    =IF($I$4="","",SUMIF(SU,"Crate",CU_FT))
    =IF(I4="","",SUMIF(SU,"Crate",Lbs))


    Everything works great…except when I apply the Advanced Filter to the list. I just want the summary section to reflect the visible cells. Which it does not.


    I'm not opposed to calculating these figures with VBA either.


    Thanks in advance for any help that can be provided.


    Phil

  • VBA Calendar Control without Control

    Hey Gops/Squire King,


    I think Squire King is right...I have heard other responses like this one in the past and they have related to the UK date format.


    I cannot however duplicate the problem on my machine even when I change to UK date formats, thus I cannot repair the issue. I do feel it's in the code and seem to recall someone else getting around the problem. I believe it was when we could not attach workbooks to the ozgrid site and I don't believe I got the revisions. If I had I would have worked it into my calendar.


    If someone out there has resolved this, please attached it to this thread and I'll update my latest version and reattach it for everyone’s use.



    Best regards to all.


    Phil

  • Create a shortcut on desktop

    Might someone know how I could create a shortcut to another on my desktop?


    I can copy a file with the FSO (FileSystemObject) but I'm not sure how to create a shortcut.


    Additionally, I'd like to change the icon and name of that shortcut.



    Again, any help is appreciated.


    Phil

  • Load Fonts

    Does anyone how I could load fonts using vba in an Excel module.


    I've use the FSO (FileSystemObject) to copy the fonts to the proper directory. This however does not make them active.


    Any suggestions???


    Thanks,


    Phil

  • Changing Regional Date Settings

    Might someone out there know how to change the Regional Dates settings (usually done in the control panel) with vba.


    I have a setup file that loads a Excel application. In this application it is necessary to change the date settings from the default "m/d/yyyy" to "m/d/yy".


    Ideally this solution would work on Windows 98 through XP.


    Any help would be appreciated.


    Phil

  • VBA Calendar Control without Control

    I'm currently updating this tool and I'd like to add all the functionality possible.


    Along those lines. Gops had raised a great point about calling up the calendar if any cell is selected that is formatted as a date. Derk had responded with...

    Quote


    If IsDate(target) then


    Which sounded great but it's looking for a cell value not the cell format.


    Does anyone else have any ideas along the same as Drek's?


    As always, any help would be appreciated.


    Phil

  • VBA Calendar Control without Control

    Hey Gops,


    This is no trouble at all. In fact I had this feature in a previous version and had moved away from it for some odd reason. However, what you say makes perfect sense and I'll update with the select button.


    Additionally, I want to play around with some radio buttons on the month and year as well as the drop downs to improve that functionality.


    Perhaps I'll get to that today. I'll forward to you the latest when complete.


    (It's to bad the attach feature is not working, sure would be nice in this case.)


    If anyone else would like the new version...Drop a line on this thread or send me an email and I'll forward.


    Best regards to all.


    Phil


    PS…Maybe we can talk Dave into making this utility available for download somewhere on the Ozgrid site???

  • Controls (Control Toolbox) for UserForms: Is this possible?

    Try the below code, it has worked well for me in several apps.


    ' This example is based on a tip by J.G. Hussey,
    ' published in "Visual Basic Programmer's Journal"


    Sub RemoveDuplicates()
    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim i As Integer, j As Integer
    Dim Swap1, Swap2, Item

    ' The items are in A1:A105
    Set AllCells = Range("A1:A105")

    ' The next statement ignores the error caused
    ' by attempting to add a duplicate key to the collection.
    ' The duplicate is not added - which is just what we want!
    On Error Resume Next
    For Each Cell In AllCells
    NoDupes.Add Cell.Value, CStr(Cell.Value)
    ' Note: the 2nd argument (key) for the Add method must be a string
    Next Cell


    ' Resume normal error handling
    On Error GoTo 0


    ' Update the labels on UserForm1
    With UserForm1
    .Label1.Caption = "Total Items: " & AllCells.Count
    .Label2.Caption = "Unique Items: " & NoDupes.Count
    End With

    ' Sort the collection (optional)
    For i = 1 To NoDupes.Count - 1
    For j = i + 1 To NoDupes.Count
    If NoDupes(i) > NoDupes(j) Then
    Swap1 = NoDupes(i)
    Swap2 = NoDupes(j)
    NoDupes.Add Swap1, before:=j
    NoDupes.Add Swap2, before:=i
    NoDupes.Remove i + 1
    NoDupes.Remove j + 1
    End If
    Next j
    Next i

    ' Add the sorted, non-duplicated items to a ListBox
    For Each Item In NoDupes
    UserForm1.ListBox1.AddItem Item
    Next Item


    ' Show the UserForm
    UserForm1.Show
    End Sub



    Hope this helps.



    Phil

  • VBA Calendar Control without Control

    I have updated this Calendar considerably to include:


    1. Some event handlers that call up the calendar when any cell that is formatted as a date are selected.
    2. Use of the calendar date picker to select a date to be inserted into a user form.
    3. Show years past and future.


    I also have another version that uses a class module instead of redundant lines of code for each button.


    If anyone wants either version, email me and I'll gladly forward as freeware.


    When the attachments work again I’ll post the new versions.


    Phil

  • Tricky sorting issue

    Survey says........Wrong answer.


    The merged cells are not allowing it to sort. I know that I should avoid using merged cells but in this case it does have a strong benefit.


    So I guess that I'm back to trying to figure out how to sort the entire report yet keeping the blocks of 3 rows together.


    Any help would be appreciated.


    I'll continue to plug away and try to figure it out regardless.


    Phil

  • Tricky sorting issue

    Hey Tom,


    Thanks for the reply and looking into the issue.


    However, I had already considered that option and dismissed it for I wanted to sort by the ship date.


    That's it! :eureka: I'll plug the ship date in that last column 3 times then the job number /a,b,c in the next. Then I can write a sort routine that uses the new last 2 column's for the sort criteria.


    I love it when it comes together.


    Thanks for the help.


    Phil