Posts by vwankerl

Re: Attempting to use dependent checkboxes to autofilter data

That is a good start! If you can experiment with the code i sent you, you might find that if you select omit cancelled and leave complete un checked, you would see all including complete but not cancelled (and of course vise versa for complete). If you want to see everything then leave omit and show unchecked for both cancelled and complete. If you only want to see cancelled then click show for cancelled (and vice versa for complete). I am not sure you need to add the Include button to accomplish the task.

If CriteriaOne = "" Then
        If CriteriaTwo = "" Then
            Selection.AutoFilter Field:=1  ' do this if no Show/Omit selections made
        Else
            Selection.AutoFilter Field:=1, Criteria1:=CriteriaTwo & "Complete"  ' do this if you only selected Complete option
        End If
    Else
        If CriteriaTwo = "" Then
            Selection.AutoFilter Field:=1, Criteria1:=CriteriaOne & "Cancelled"  ' do this if you only selected Cancelled option
        Else
            If CriteriaOne = "=" And CriteriaTwo = "=" Then
                Selection.AutoFilter Field:=1, Criteria1:=CriteriaOne & "Cancelled", Operator:=xlOr, Criteria2:=CriteriaTwo & "Complete" ' do this if you selected Show for both Cancelled and Complete
            Else
                Selection.AutoFilter Field:=1, Criteria1:=CriteriaOne & "Cancelled", Operator:=xlAnd, Criteria2:=CriteriaTwo & "Complete" ' do this if you made a Show selection on one and an Omit selection on the other
            End If
        End If
    End If

I added some comments to the AutoFilter setup lines above. They cover pretty much every option that makes logical sense. Try the various options with the example I sent and see if they don't do the job. I am signing off for the night.

Re: Questions for a Judging Spreadsheet (Linking 2 separate cells/ calculating averag

When you say "remove the min and max scores and then figure the average", do you mean to actually erase the min and max or do you mean don't use those in the average? I think I know the answer but just want to be sure.

Re: Attempting to use dependent checkboxes to autofilter data

I have attached the file I made to get the code I sent earlier. That way you can see the UserForm I put together that goes with the code. Any questions, just ask.

Re: Attempting to use dependent checkboxes to autofilter data

I am sorry about not mentioning about the Ok button. The code for the Ok button is the code labeled CommandButton1_Click. Do you know how to work with the Properties of the UserFrom and its Controls? There are lots of things you can do just by managing those properties. Lots of tutorials that cover UserForm programming available here on OzGrid and elsewhere and almost every book about VBA or Visual Basic has at least one chapter on UserForms. If you need more help just ask though.

Re: Attempting to use dependent checkboxes to autofilter data

To do the OptionButtons in a Frame, put a Frame on the UserForm canvas - make it bigger than necessary and make it pretty later. Now place two OptionButtons into the Frame. They will become linked together automatically by putting them into the Frame. You can change the properties of each separately but they will remain linked. I hope that helps.

Re: Attempting to use dependent checkboxes to autofilter data

One other thing, the reason your idea is not working quite right is that you are calling the macros too early in the option selection process. You have to delay the macro call until the user has an opportunity to make all the desired selections. That is why I suggested that you use OptionButtons in a Frame instead of Checkboxes since OptionButtons (sometimes called RadioButtons) allow one or the other but not both and the combined value can be accessed from the Frame. It is also why you need the Ok commandbutton to signal when the selection process is done so you can perform the macro. That is why Excel 2003 AutoFilter forces you to use (Custom) and displays a dialog box to set up.

Re: Attempting to use dependent checkboxes to autofilter data

In reference to my previous, this allows and of the various combinations of the Show/Omit selections and attempts to setup the AutoFilter accordingly. if the logic is not accurate, you can make changes as necessary. Note the two last AutoFilter statements (the longest ones that contain 2 criteria) are of opposite Operators. That is so the instance of "=" (Show) for both Cancelled and Complete will show only those but any time the "<>" (Omit) is specified it works differently (Boolean logic raising its head).

Re: Attempting to use dependent checkboxes to autofilter data

I would recommend that instead of using the CheckBox control you used two OptionButton controls (one for Show and one for Omit) for each selection and put them in a Frame (two OptionButtons per Frame), add an Ok button to signal when you are done making your selections, and have some code like this:

Option Explicit


Private Sub CommandButton1_Click()
    Dim CriteriaOne As String, CriteriaTwo As String
    Select Case Frame1.Tag
        Case ""
            CriteriaOne = ""
        Case "Omit"
            CriteriaOne = "<>"
        Case "Show"
            CriteriaOne = "="
    End Select
    Select Case Frame2.Tag
        Case ""
            CriteriaTwo = ""
        Case "Omit"
            CriteriaTwo = "<>"
        Case "Show"
            CriteriaTwo = "="
    End Select
    If CriteriaOne = "" Then
        If CriteriaTwo = "" Then
            Selection.AutoFilter Field:=1
        Else
            Selection.AutoFilter Field:=1, Criteria1:=CriteriaTwo & "Complete"
        End If
    Else
        If CriteriaTwo = "" Then
            Selection.AutoFilter Field:=1, Criteria1:=CriteriaOne & "Cancelled"
        Else
            If CriteriaOne = "=" And CriteriaTwo = "=" Then
                Selection.AutoFilter Field:=1, Criteria1:=CriteriaOne & "Cancelled", Operator:=xlOr, _
Criteria2:=CriteriaTwo & "Complete"
            Else
                Selection.AutoFilter Field:=1, Criteria1:=CriteriaOne & "Cancelled", Operator:=xlAnd, _
Criteria2:=CriteriaTwo & "Complete"
            End If
        End If
    End If
    UserForm1.Hide
    ResetFields
End Sub


Private Sub ResetFields()
    Frame1.Tag = ""
    Frame2.Tag = ""
    OptionButton1.Value = False
    OptionButton2.Value = False
    OptionButton5.Value = False
    OptionButton6.Value = False
End Sub


Private Sub OptionButton1_Click()
    Frame1.Tag = "Show"
End Sub


Private Sub OptionButton2_Click()
    Frame1.Tag = "Omit"
End Sub


Private Sub OptionButton5_Click()
    Frame2.Tag = "Show"
End Sub


Private Sub OptionButton6_Click()
    Frame2.Tag = "Omit"
End Sub


Private Sub UserForm_Initialize()
    Frame1.Tag = ""
    Frame2.Tag = ""
End Sub

And of course, you need some code in a Module to Show the form.

Re: Click on active row to have data appear in created Userform - excel 2003

I am working on the other request from you. I don't think it is an acceptable use to post the same request multiple times.

Re: VBA to click on active row and have it appear in Userform

This looks like an interesting challenge. I will play around with it and let you know what I come up with. One question, You say there are 16 columns and 16 textboxes. Which textbox is for the column labeled "Case #"? I may have other questions, but I think I understand what you want to do.

Re: Is it possible to include Multiple Delimiters in Workbook.Open Function?

Based on the VDE Help in Excel 2003 and Excel 2007 the Workbooks.Open method parameter "Delimiter" only uses the first character in the string passed to it. If you have to open multiple text files with different delimiters you will need to use VBA to query the user for the files and associated delimiters. Done right, the amount of user interaction could be minimal but it will still require some programming.

Re: textbox option button combo box

After looking over your question and the attached spreadsheet, my recommendation is that you need to use some tutorials for doing VBA Userforms. Pay particular attention to using the Userform and Controls Properties. It is there that you will find ways to hide controls and make them visible using VBA. All that you want to do is doable and there are lots of people here who could do it for you, but you would probably not learn anything and be back asking for features or options. Get some tutorials (and maybe a couple VBA books, e.g. Professional Excel Development from Amazon - the current bible for VBA development). Lots of help there.

Re: Rearrange cells by specific criteria

My first thought was to use a PivotTable also. I am pretty rusty on those so can't help there. If you need to do this with older versions of Excel let us know but you will need to do some VBA probably.

Re: Create separate files &amp; mail through Outlook

Here is a code segment from an app I use quite a bit that will, from VBA, build and send an Outlook message with a file attachment. There is another more generic way to send an email from VBA but it does not allow all the features that you have with this method. This is pretty well documented at various places on the internet - search for Excel VBA send email Outlook.

Here are a couple links: http://www.ozgrid.com/VBA/send-email.htm and http://www.rondebruin.nl/sendmail.htm
There are many others.

Note, this segment assumes you have already created a workbook file and have the name and path of this file to use for the attachment.

Set objOutlookApp = CreateObject("Outlook.Application")
    Set objOutlookMail = objOutlookApp.CreateItem(0)
    On Error Resume Next
    With objOutlookMail
        .To = mSendTo  ' email address to send to
        .CC = ""
        .BCC = ""
        .Subject = "Some text for the subject line "
        .Body = mBody  ' Some text to put in the body of the email
        .Attachments.Add (strFullPathName)  ' name with full path for the file to attach
        .Send
    End With

Hope it works for you.

Re: Copy 700 times

Here is my offering. I am looping through each cell rather than copying blocks of cells. Also, you are not limited to 700. Note that I am also copying the StormId and the lat/long from each row of Help so you can have the reference without having to maintain the Fill to match the other sheets. Let us know how these work for you.

Option Explicit


Const ColDU As Long = 125
Const LatLongStartRow As Long = 2
Const LatLongStartCol As Long = 3
Const HelpLatRow As Long = 2
Const HelpLongRow As Long = 3
Const HelpColOffset As Long = 1
Const HelpDataStartRow As Long = 4
Const FillStartRow As Long = 2
Const FillStartCol As Long = 3


Sub CopiaPega()
    Dim StormIdIndex As Long, StormId As String, ColIndex As Long, ColId As String
    StormIdIndex = LatLongStartRow
    With Worksheets("Lat")
        Do Until IsEmpty(.Cells(StormIdIndex, 1))
            StormId = .Cells(StormIdIndex, 1).Value
            ColIndex = LatLongStartCol
            Do Until IsEmpty(.Cells(StormIdIndex, ColIndex))
                CopyLatLong StormIdIndex, ColIndex
                ColIndex = ColIndex + 1
            Loop
            CopyToFill StormId
            StormIdIndex = StormIdIndex + 1
        Loop
    End With
End Sub
'
Sub CopyLatLong(RowIndex As Long, ColIndex As Long)
    Dim StormId As String
    With Worksheets("Help")
        StormId = Worksheets("Lat").Cells(RowIndex, 1).Value
        .Cells(HelpLatRow, ColIndex + HelpColOffset).Value = Worksheets("Lat").Cells(RowIndex, ColIndex).Value
        .Cells(HelpLongRow, ColIndex + HelpColOffset).Value = Worksheets("Long").Cells(RowIndex, ColIndex).Value
    End With
End Sub


Sub CopyToFill(StormId As String)
    Dim HelpIdIndex As Long, HelpId1 As String, HelpId2 As String
    Dim FillColIndex As Long, FillRowIndex As Long
    FillColIndex = FillStartCol
    With Worksheets("Fill")
        Do Until IsEmpty(.Cells(1, FillColIndex))
            FillColIndex = FillColIndex + 1
        Loop
        .Cells(1, FillColIndex).Value = StormId
        HelpIdIndex = HelpDataStartRow
        FillRowIndex = FillStartRow
        Do Until IsEmpty(Worksheets("Help").Cells(HelpIdIndex, 1))
            .Cells(FillRowIndex, 1).Value = Worksheets("Help").Cells(HelpIdIndex, 1).Value
            .Cells(FillRowIndex, 2).Value = Worksheets("Help").Cells(HelpIdIndex, 2).Value
            .Cells(FillRowIndex, FillColIndex).Value = Worksheets("Help").Cells(HelpIdIndex, ColDU).Value
            HelpIdIndex = HelpIdIndex + 1
            FillRowIndex = FillRowIndex + 1
        Loop
    End With
End Sub

Re: Code work in Excel 2007 gets error in 2003

Just a quick reply: There are major differences between 2003 and 2007 when it comes to Sorting. Just look at the differences with the Sort dialogs between the two. Likewise, the Sorting in Macros is very different. If you have a macro that uses sorting in Excel 2003 it might very well work in 2007, but going the other way is not a good idea unless you understand all the differences. You may have to design a different sort process for the 2003 macro. If you are going to support older Excel versions, you really need to have test systems with all the supported versions of Excel so you can figure out the gotcha's between versions.

Re: Copy 700 times

I will play with this a little and see what I can do.

Re: Copy 700 times

I think I understand this question but need a little more information. You say you "need the maximum which stands in column DU of the Help tab to be copied". I note that column DU in the Help tab has a Min function , not a Max function. Are you saying that you need to values that are in column DU of the Help tab to be copied to the Fill tab or do you need a Max function to be performed before coping to the Fill tab?

I am trying to capture all the ways a user may choose to Copy, Cut and Paste. I currently have hot keys covered, but need a way to capture the Standard Toolbar clicks and the Edit menu clicks. This could also be extended to other functions on the Standard toolbar and menus. I am fairly expert in VBA.