Free Microsoft Excel 2013 Quick Reference

Find method in user form Results

Hi

I have placed the Find Method in User Form into my spreadsheet and run it from a command button however it returns a compile error: Ambiguous name detected: ListBox1_DblClick. What am I doing wrong as this a direct copy & Paste

also, I will enventually want to change the search parameters from Column 1, 2 & 3 to 4, 5 & 6 (Columns D,E & F). Is it a simple as changing the code in blue:

	VB:
	
[COLOR=blue]inside the table 
ComboBox1.RowSource = .Columns(1).Offset(1, 0).Address 
ComboBox2.RowSource = .Columns(2).Offset(1, 0).Address 
ComboBox3.RowSource = .Columns(3).Offset(1, 0).Address[/COLOR] 
[COLOR=#0000ff][/COLOR] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there anything else I need to be thinking about?

Hi,

I have a form that submits data to master workbook. Before that happens it needs to be validated to ensure records are completed. The last row of each of the data worksheets has a formula to ensure that each record has the required number of fields completed.

The rub is this: I have created a collection object that contains the data worksheets that the form writes to. This macro "should" loop through each worksheet in the collection and check the last column for the value of each formula present. If it finds the value "false" it should alert the user to this and open the appropriate multipage for the user to rectify that record.


	VB:
	
 FinishButton_Click() 
     
    Dim r As Range, strstartC As String, ws As Worksheet 
     
     'Test 1: Check value reporting sheets to ascertain
     'if they contain completed records
    myCollection 'Initialises the ws col.obj
    For Each ws In wsCollection 
        If Not ws.Range("A2") = vbNullString Then 'Source sheet has data
            With Worksheets(ws.Name) 
                strstartC = .Range("IV1").End(xlToLeft).Address 
                Set r = Cells.Find(What:=False, _ 
                After:=strstartC, LookIn:=xlValues, lookat:=xlWhole, _ 
                SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ 
                MatchCase:=False) 
            End With 
            If Not r Is Nothing Then 'Match
                MsgBox "Records Incomplete!", vbExclamation, "Records Editor" 
                Select Case ws.Name 
                Case "Type1" 
                    MultiPage1.Value = 2 
                Case "Type2" 
                    MultiPage1.Value = 3 
                Case "Type3" 
                    MultiPage1.Value = 4 
                Case Else 
                End Select 
                Exit Sub 
            End If 
        End If 
    Next ws 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have used the find method in the past and had the desired result. But that was with explicitly defined starting points. As this is a dynamic starting point I am getting a "Type Mismatch" error and the find method highlighted in VBE.

My questions are:

How do I perform a find where the starting point is dynamic?

If this is possible, how can I get the row number of the found item (to pass it to another macro)?

TIA.

I have a two column listbox on a user form.

Two of my command buttons change the value in the second column for the item selected. The value can be empty (null, blank, etc.) or a "1".

The method I'm using to populate the listbox makes it so that value change is not automatically seen, so after a change is made I've added code for the listbox to repopulate so the chanage can be seen.

Repopulating it shows the value changed to, or from, a "1", but the listbox would always return to the top of the list after it was repopulated. This effectively makes you lose track of where you were in the list.

My next step was to remember the the ListIndex value for the item selected and then after the listbox was repopulated I set the listbox ListIndex value to that previous value.

That works fine, as far as if I there are 100 items on my list, and I changed item number 75, it will reselect that same item number 75, but it is always the very bottom of the window.

In other words, lets say item number 75 was was in the very middle of the visible listbox window when I selected it. So you could see items number 60 though 90 visible in the window. After I repopulate the ListBox and reset the ListIndex number, you will see item number 75 selected, but it will be at the very bottom of the ListBox window and in the window you will see item numbers 45 through 75, instead of item numbers 60 through 90.

I'd like to find a way to set the listbox to scroll to the very same position it was in before I repopulated it, so the user doesn't even know I've repopulated the ListBox. All I want them to see is that value in the second column to chanage to, or from a "1".

I've searched for hours for a solution and the closest I've come is the Userform Scroll event, but that does not seem to apply to a ListBox on a user form.

Is it possible to remember the scroll position of a ListBox and then reset the ListBox to that same position after it is repopulated?

Hello guys,

I have a macro with a user form where in a list box are displayed all tasks that are assigned to a user.
The macro below is a find method where every time the username is met is collected into array and later distributed into the listbox.
So far this is using only one search criteria - the username but i also need to add a second search criteria - "task status" which lets say is in column K:K.
What i need is: when the macro finds the user to check the "task status" and if it is different from "approved" to skip this entry and go one with the next row. Basically in the list box i need to have displayed only those tasks that are assigned for the specific user and with status "approved".
Any suggestions?


	VB:
	
 
With Workbooks("DB_AIM.xls").Sheets("Data").Range("O:O") 'column O:O contains the user names
    i = 1 
     
     
     'will show all the task asigned to a team member
     
     
    Set MyValue = .Find(what:=MyUser) 
     
    If Not MyValue Is Nothing Then 
         
         
        Redim test(1 To 7, 1 To i) 
        Redim Preserve test(1 To 7, 1 To i) 
         
        Start = MyValue.Address 
        CurrRow = MyValue.Row 
         
        test(1, i) = .Cells(CurrRow, -13).Value 
        test(2, i) = .Cells(CurrRow, -10).Value 
        test(3, i) = .Cells(CurrRow, -9).Value 
        test(4, i) = .Cells(CurrRow, -8).Value 
        test(5, i) = .Cells(CurrRow, -11).Value 
        test(6, i) = .Cells(CurrRow, -12).Value 
        test(7, i) = .Cells(CurrRow, -2).Value 
         
        i = i + 1 
         
        Do 
            Set MyValue = .FindNext(MyValue) 
            CurrFind = MyValue.Address 
             
            If CurrFind = Start Then Goto 16 
             
            Redim Preserve test(1 To 7, 1 To i) 
             
            CurrRow = MyValue.Row 
             
            test(1, i) = .Cells(CurrRow, -13).Value 
            test(2, i) = .Cells(CurrRow, -10).Value 
            test(3, i) = .Cells(CurrRow, -9).Value 
            test(4, i) = .Cells(CurrRow, -8).Value 
            test(5, i) = .Cells(CurrRow, -11).Value 
            test(6, i) = .Cells(CurrRow, -12).Value 
            test(7, i) = .Cells(CurrRow, -2).Value 
             
            i = i + 1 
             
        Loop Until CurrFind = Start 
         
    Else 
         
        Exit Sub 
        Workbooks("DB_AIM.xls").Close 
         
         
    End If 
    16 
    IdeaApproved.Column = test 
     'IdeaApproved.List = Application.Transpose(test)
    With IdeaApproved 
        .ColumnWidths = "20;190;80;110;70;80;60" 
    End With 
     
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi, I am new to VBA and is muddling through it. I am trying to create a form so that a couple of us can enter student lunch choices for our school. This is what I have:

I have a workbook with 2 spreadsheets: Orders and Students. I have created a form that will be activated by a command button in the Orders spreadsheet. This Order form looks like a single month. Each day on this form is a frame. In the frame are 1 to 3 checkboxes and corresponding textbox(s). The checkboxes lists the lunch choices available for the day. The textbox(s) is(are) for the user to enter the number of lunches for the checked lunch (i.e. student wants 3 slices of pizza). There is a combo box which contains the students in the school. The combo box is populated by a named range in rowsource. The named range points to a column on the Students spreadsheet. I also have some fields that will show the total cost for the selected lunches (except for pizza, all lunches cost the same). There are also a save, clear, and exit button. The save button will write a row for each lunch choice to the Orders spreadsheet. The clear button will reinitialize the form. The exit button will return to the Orders spreadsheet and close the form.

Onchange in the combobox, I want to use the Find method to search for the student in the Student spreadsheet to pull in the class and homeroom data.

Now, my first set of (many) questions, can I use the Find method to search the Student spreadsheet? How do I pull the Class (column C) and Homeroom (column D) and populate 2 label fields in the form.

Here is what I have (I have not even tested this code yet):

Dim wks As Worksheet
Dim rFoundResult As Range
Dim sLookingFor As String
Set sLookingFor = cboStudent.Value
Set wks = Worksheets("Students")
wks.Activate
wks.Range("A1").Activate -- where student name is located

Set rFoundResult = wks.Cells.Find(What:=sLookingFor, _ after:=wks.Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, _ SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

If rFoundResult Is Nothing Then
labGrade.Caption = ""
labHomeroom.Caption = "Student NOT FOUND"
Else
labGrade.Caption = -- put grade from Student here
labHomeroom.Caption = -- put homeroom from Student here
End If

Hi Everybody,

I would like to enable a user to draw free hand on a user form, and also have my program draw things.

I use to use VB6 many moons ago, and I used a the picturebox control and the draw method for this. Im sure things have changed, but I cant seem to find even a similar control in Excel VBA (even when going to tools - additional controls). Is there one that I can use?

I did find this post in the archive
http://www.mrexcel.com/archive2/74900/86993.htm
but dont quite understand the answer.

Can anyone help me out?
Thanks
-gilman

Hey there,
I have a user form I have been working with but I just can't seem to
get my Combo Boxes to work. No matter what I do I get an error
Run-Time Error 1004
Method Range of Object Worksheet Failed

The following is what I have so far: I put ******* where I am gettting
the error. I would appreciate any ideas. Oh yeah and this is very first
userform so go easy on me. I need lots of hand holding

Option Explicit

Private Sub btnClose_Click()
Unload Me
End Sub

Private Sub cboInvoice_Change()
ShowValue cboInvoice
End Sub

Private Sub cboCustomerID_Change()
ShowValue cboCustomerID
End Sub

Private Sub ShowValue(cbo As ComboBox)
If cbo.ListIndex = -1 Then Exit Sub
lblShow.Caption = cbo.Value

End Sub

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ProduceData")

' find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

' check for an invoice number
If Trim(Me.cboInvoice.Value) = "" Then
Me.cboInvoice.SetFocus
MsgBox "Please enter or choose invoice number"
Exit Sub
End If

' check for a date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter a date"
Exit Sub
End If
' copy the data to the database
ws.Cells(iRow, 1).Value = "=R[-1]C+1"
ws.Cells(iRow, 2).Value = Me.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If

'clear data to enter new pallet
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
txtPallet.SetFocus
End Sub
Private Sub cmdNewInvoice_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ProduceData")

' find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

' check for an invoice number
If Trim(Me.txtInvoice.Value) = "" Then
Me.txtInvoice.SetFocus
MsgBox "Please enter an invoice number"
Exit Sub
End If

' check for a date
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter a date"
Exit Sub
End If
' copy the data to the database
ws.Cells(iRow, 1).Value = "=R[-1]C+1"
ws.Cells(iRow, 2).Value = Me.cboInvoice.Value
ws.Cells(iRow, 3).Value = Me.txtDate.Value
ws.Cells(iRow, 4).Value = Me.cboCustomerID.Value
ws.Cells(iRow, 5).Value = "None"
ws.Cells(iRow, 7).Value = Me.txtPallet.Value
ws.Cells(iRow, 8).Value = Me.txtQty.Value
ws.Cells(iRow, 9).Value = Me.txtBoxSold.Value
ws.Cells(iRow, 10).Value = Me.txtPrice.Value
ws.Cells(iRow, 11).Value = Me.txtFrt.Value
ws.Cells(iRow, 13).Value = "0"
ws.Cells(iRow, 14).Value = "0"
ws.Cells(iRow, 12).Value = "Sale"

'select a produce item via button
If Me.optSnap.Value Then
ws.Cells(iRow, 6).Value = Me.optSnap.Caption
Else
ws.Cells(iRow, 6).Value = Me.optSno.Caption
End If
'clear the data
Me.cboCustomerID = ""
Me.cboInvoice = ""
Me.txtPallet.Value = ""
Me.txtQty.Value = ""
Me.txtBoxSold.Value = ""
Me.txtPrice.Value = ""
Me.txtFrt.Value = ""
Me.cboInvoice.SetFocus
End Sub

Private Sub txtDate_Enter()
txtDate.Value = Format(Me.txtDate, "")
End Sub
Private Sub txtFrt_Enter()
txtFrt.Value = Format(Me.txtFrt.Value, "")
End Sub

Private Sub txtFrt_Exit(ByVal cancel As MSForms.ReturnBoolean)
txtFrt.Value = Format(Me.txtFrt.Value, "$#,###,###.00")
End Sub

Private Sub txtPrice_Enter()
'Places currency formatting on price
txtPrice.Value = Format(Me.txtPrice.Value, "")
End Sub
Private Sub txtPrice_Exit(ByVal cancel As MSForms.ReturnBoolean)
'as you exit then places currency formatting
txtPrice.Value = Format(Me.txtPrice.Value, "$#,###,###.00")
End Sub

Private Sub UserForm_Initialize()

Dim cItem As Range

With Me.cboCustomerID
*********For Each cItem In wksLookupLists.Range("CustomerIDList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
With Me.cboInvoice
For Each cItem In wksLookupLists.Range("InvoiceList")
.AddItem cItem.Value
.List(.ListCount - 1, 1) = cItem.Offset(0, 1).Value
Next
End With
txtDate.Value = Date
End Sub

Hi, I have a Excel Form on which I need to run a Macro to check some key cells and if the cells are empty, I want to inform the user which cell is empty and then have the macro go to the empty cell.

There may be multiple empty cells in the form

I have setup the following macro, but it doesn't seem to work. It only finds the first empty cell and then ignores any other empty cells.

Also not sure how to get the macro to return the user to each empty cell for required actions
I have added the macro I have put together below. Any assistance with this would be great.


	VB:
	
 Check_For_Empty_Fields() 
     ' When user hits ADD RECORD Button, Macro checks for Missing Field Data for required upload file
    If Range("J58") = "" Then 
        MsgBox ("Please fill in Supplier Name in Section 3.") 
        Exit Sub 
    ElseIf Range("J60") = "" Then 
        MsgBox ("Please fill in Street Address 1 in Section 3.") 
        Exit Sub 
    ElseIf Range("J62") = "" Then 
        MsgBox ("Please fill in Street Address 2 in Section 3.") 
        Exit Sub 
    ElseIf Range("J64") = "" Then 
        MsgBox ("Please fill in Suburb in Section 3.") 
        Exit Sub 
    ElseIf Range("J66") = "" Then 
        MsgBox ("Please select State in Section 3.") 
        Exit Sub 
    ElseIf Range("P66") = "" Then 
        MsgBox ("Please fill in Postcode in Section 3.") 
        Exit Sub 
    ElseIf Range("Y64") = "" Then 
        MsgBox ("Please fill in Supplier Email Address in Section 3.") 
        Exit Sub 
    ElseIf Range("Y66") = "" Then 
        MsgBox ("Please fill in Remittance Method in Section 3.") 
        Exit Sub 
    ElseIf Range("J72") = "" Then 
        MsgBox ("Please select in AR Contact Telephone Area Code in Section 3.") 
        Exit Sub 
    ElseIf Range("N72") = "" Then 
        MsgBox ("Please fill in AR Contact Telephone Number in Section 3.") 
        Exit Sub 
    ElseIf Range("P72") = "" Then 
        MsgBox ("Please fill in AR Contact Telephone Number in Section 3.") 
        Exit Sub 
    ElseIf Range("J74") = "" Then 
        MsgBox ("Please select in AR Contact Fax Area Code in Section 3.If Fax Number Not Applicable select (NA) from list") 
        Exit Sub 
    ElseIf Range("N74") = "" Then 
        MsgBox ("Please fill in AR Contact Fax Number in Section 3.If Fax Number Not Applicable enter 0000 into field") 
        Exit Sub 
    ElseIf Range("P74") = "" Then 
        MsgBox ("Please fill in AR Contact Fax Number in Section 3.If Fax Number Not Applicable enter 0000 into field") 
        Exit Sub 
    ElseIf Range("D78") = "" Then 
        MsgBox ("Please fill in ABN number in Section 3.") 
        Exit Sub 
    ElseIf Range("J78") = "" Then 
        MsgBox ("Please fill in ABN number in Section 3.") 
        Exit Sub 
    ElseIf Range("F84") = "" Then 
        MsgBox ("Please fill in Bank BSB Number in Section 3.") 
        Exit Sub 
    ElseIf Range("F86") = "" Then 
        MsgBox ("Please fill in Bank Account Number in Section 3.") 
        Exit Sub 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Regards
Lance

Hi

I have read about the dir and msg methods in help. But cannot seem to figure out how to get them to work in a cleaver fashion.

I basically want to pick a string value from cell(1,1). This string will be concatonated to another string ('.html') to form a file name. If this file name exists already on the d:/ drive it will give the user a yes/No message box. If they select yes it will overwrite the existing file. If they select no it will just got back to the active sheet (sheet1). The contents of the file is irrelevant.

Any suggestions?

Hello all,
I have a worksheet with a few thousand entries. It is a list of every book in a particular law firm. I was asked to write a macro to search the books and return a list of the books on a seperate worksheet. I was thinking of using a user form so the users can easily input data but I am having a tough time finding where to start for the search. I tried to record a macro using the built in find method but that didnt work out. PLEASE HELP

Thanks in advance
Bob Eckels
(CAMBYSES)

I am trying to get this sub to look at a variable workbook as indicated by Dim ex As Variant. I have this working, now I am having a problem with this statement:

rngFind = Windows(ex).Range("A2:A" & End_Row).Find(what:=lookVal)

I get this runtime error: Object doesn't support this property or method

With the debugger the ex is showing the correct workbook and lookVal is showing the correct value. I'm guessing it is not liking the .Range("A2:A" & End_Row). As End_Row is showing a 0.

I am having the user enter the loan number to be searched for in a textbox, then once the command button is pressed the code needs to search the ex workbook for the loan number and pull cetain data from it into the user form (which I have had working) and if the lookVal is not found use a Msgbox.

Code:
Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim ex As Variant
    Dim lookVal As Variant
    Dim rngFind As Range
    Dim End_Row As Long

    ex = Me.lblExtract.Caption

    lookVal = Me.txtLoanNumber.value

    rngFind = Windows(ex).Range("A2:A" & End_Row).Find(what:=lookVal)

    End_Row = Range("A" & Rows.Count).End(xlUp).Row

    Windows(ex).Activate
    Sheets("Borrower,Master,ARM").Select

    If rngFind Is Nothing Then
        'not found
        MsgBox "Sorry, that loan number was not found in the list!", vbInformation, "ERROR!"
    Else
        With Me.lblCPB
            .Caption = rngFind.Offset(0, 10).Text
        End With
        With Me.lblNoteType
            .Caption = rngFind.Offset(0, 20).Text
        End With
        With Me.lblHoldCodes
            .Caption = rngFind.Offset(0, 85).Text
        End With
        With Me.lblServiceType
        If rngFind.Offset(0, 90).Text = "1" Then
            .Caption = "Master Serviced"
        End If
        If rngFind.Offset(0, 90).Text = "L" Then
            .Caption = "Limited Serviced"
        End If
        If rngFind.Offset(0, 90).Text = "" Then
            .Caption = "Primary Serviced"
        End If
        End With
    End If
    Application.ScreenUpdating = True

End Sub
I can't hard code the workbook as this form is used to copy data from one variable workbook to a separate variable workbook (which is working)

I am just working on validation for the loan number that is entered.

Thanks for your help!

Hey all,

I've been scouring the internet trying to find a solution to this one and I keep hitting a wall. I have a userform and I would like people to be able to click on a txtbox and a calendar pop up. they select a date, hit ok and it populates the txt box with that date. I have made the calendar form and I am able to get it to appear but how do I get it to put the date back into the txtbox? There are multiple date txtboxes on the sheet and I need to populate each one individually as you click on them. All of the methods I have found so far are about populating a field in excel Any help anyone?Dave

I see a good many questions nowadays about how we can use controls on
a User Form to dictate changes to a SQL query for returning records to
Excel from a database.

Good news I guess... It means more people are getting into using VBA & ADO
to talk to their databases.

Anyway, I thought I would post up an example of how you can use UserForm controls
to determine a SQL query to either return ALL records from a table or
via use of a checkbox and combobox, filter the records to return only a subset.

The attached example uses the Northwind database, as this comes as standard with
any Access installation. You may need to amend the path to your copy of Northwind.
To do this, edit the stCon variable at the top of the UserForm code module (shown below
also).

Make sure you also have a reference set to Microsoft ActiveX Data Components v2.5 or later
in your VBE.

For those who cannot download files/attachments... the code is displayed below.

With thanks as always to my friend Dennis Wallentin, especially for teaching me about
Error Checking methods with ADO.


	VB:
	
 
 '#########################################################
 '#                                                       #
 '# References need to be set in the VBE to the following #
 '# reference libraries:-                                 #
 '# Microsoft ActiveX Data Objects 2.5 or > Library       #
 '#                                                       #
 '#########################################################
 
 'You may also need to amend the path to Northwind Database in the connection string below
 
Const stCon    As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb;" & _ 
"Persist Security Info=False" 
 
 
Private Sub cmdClose_Click() 
     'close the form
    Unload Me 
End Sub 
 
Private Sub chkYr_Click() 
     'This is where you can add a filter by the year
    Dim stSQL  As String 
    Dim cnt    As ADODB.Connection 
    Dim rst    As ADODB.Recordset 
    Dim vaData As Variant 
     
     'Just select the Distinct Years from Orders Table to load into Year Combobox
    stSQL = "SELECT DISTINCT DatePart(""yyyy"",[OrderDate]) FROM ORDERS;" 
     
    If chkYr.Value = True Then 
         'if the year filter checkbox is checked
        Set cnt = New ADODB.Connection 
        Set rst = New ADODB.Recordset 
        cnt.ConnectionString = stCon 
        With cnt 
            .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
            .Open stCon 'Open connection.
             'Execute the SQL statement.
            Set rst = .Execute(stSQL) 
        End With 
        With rst 
            Set .ActiveConnection = Nothing 'Disconnect the recordset.
             'Populate the array with the whole recordset.
            vaData = .GetRows 
        End With 
         'Close the connection.
        cnt.Close 
        With Me 
            With .cmbYr 
                .Clear 
                 'load the query result into combobox
                .List = Application.Transpose(vaData) 
                .ListIndex = -1 
            End With 
        End With 
    Else 
        With Me 
            With .cmbYr 
                .Clear 
            End With 
        End With 
    End If 
End Sub 
 
Private Sub cmdQuery_Click() 
     'run query to find records
    Dim stParam As String, stParam2 As String 
    Dim stSQL  As String 
    Dim cnt    As ADODB.Connection 
    Dim rst    As ADODB.Recordset 
    Dim fld    As ADODB.Field 
    Dim wsSheet As Worksheet, wbBook As Workbook 
    Dim i As Long, j As Long, x As Integer 
     
     'initial SQL to return all records
    stSQL = "SELECT * FROM ORDERS" 
     
     'set the parameter strings
    stParam = " WHERE DatePart(""yyyy"",[OrderDate]) = " & Me.cmbYr.Text 
    stParam2 = " ;" 
     
     'check & build variable parameters
     'depending on whether checkbox ticked by user
    If Me.chkYr.Value = True Then 
        stSQL = stSQL & stParam & stParam2 
    Else: stSQL = stSQL & stParam2 
    End If 
     
    On Error Goto ErrHandle 
     
    Set cnt = New ADODB.Connection 
    Set rst = New ADODB.Recordset 
     
    Set wbBook = ThisWorkbook 
    Set wsSheet = ThisWorkbook.Worksheets(1) 
     
    With cnt 
        .ConnectionString = stCon 
        .Open 
    End With 
     
    With rst 
        .CursorLocation = adUseClient 
        .Open stSQL, cnt, adOpenStatic, adLockReadOnly 
        .ActiveConnection = Nothing 'Here we disconnect the recordset.
        j = .Fields.Count 
        i = .RecordCount 
    End With 
     
    With wsSheet 
        .UsedRange.Clear 
        If i = 0 Then Goto i_Err 
         'Write the fieldnames to the fifth row in the worksheet
        For x = 0 To j - 1 
            .Cells(5, x + 1).Value = rst.Fields(x).Name 
        Next x 
         'Dump the data to the worksheet.
        .Cells(6, 1).CopyFromRecordset rst 
    End With 
     
    If CBool(rst.State And adStateOpen) = True Then rst.Close 
    Set rst = Nothing 
    If CBool(cnt.State And adStateOpen) = True Then cnt.Close 
    Set cnt = Nothing 
     
ExitHere: 
    Exit Sub 
     
ErrHandle: 
    Dim cnErrors As ADODB.Errors 
    Dim ErrorItem As ADODB.Error 
    Dim stError As String 
     
    Set cnErrors = cnt.Errors 
     
    With Err 
        stError = stError & vbCrLf & "VBA Error # : " & CStr(.Number) 
        stError = stError & vbCrLf & "Generated by : " & .Source 
        stError = stError & vbCrLf & "Description : " & .Description 
    End With 
     
    For Each ErrorItem In cnErrors 
        With ErrorItem 
            stError = stError & vbCrLf & "ADO error # : " & CStr(.Number) 
            stError = stError & vbCrLf & "Description : " & .Description 
            stError = stError & vbCrLf & "Source : " & .Source 
            stError = stError & vbCrLf & "SQL State : " & .SqlState 
        End With 
    Next ErrorItem 
    MsgBox stError, vbCritical, "SystemError" 
    Resume ExitHere 
     
i_Err: 
    MsgBox "There are no records for this Query" 
    Goto ExitHere 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to build a userform that allows a user to enter a search
string and click a button I have named "Find". This works fine. I
have created a second button on the user form and have titled it "Find
Next". I want the user to be able to click this button and search for
a second occurrence of the original string. I have looked at numerous
examples of the FindNext method and have noticed that they are all in
the same subroutine. I am trying to have two separate buttons with
their own subroutines. I am not having any luck making this work.
Help......

Hello,

Please look at the screen print attached of the form that I designed...

This is the search screen to search the database (Shared Excel Workbook)

My problem is that every time I search, it takes approximately 3 minutes to produce the results... Please suggest me a faster method to search through the workbook.

Database (Workbook): This is a separate shared excel workbook with 1 worksheet for each user (25 now). Each worksheet contains 45 columns of data

Structure: Everytime a request comes in to the organization,
1. Recorded and stored under the users worksheet as 1 row of data
2. Second person searches for all the records and allocates it to a 3rd person
3. Person 3 searches for all records allocated to him and resolves the request

every action performed on the record creates a new row of data with system date and time stamp on it. Record number is the only UNIQUE IDENTIFIER for all these records (<>0).

Search screen: As you can see from the attached picture, a lot of options are provided to the user to search speccific results.

Current Search method:
1. Load all the existing data to an array - Each column of data is loaded on to a separate array
2. To eliminate the multiple records, date and time stamp is compared and all records but the latest are marked (unique record number is made as 0)
----
Option Base 1
----
For I = 1 to LastRow
For J = I+1 to LastRow
IF UniqueNumberArray(I)=UniqueNumberArray(J) then
if DateTimeArray(I) > DateTimeArray(J) then
DateTimeArray(J)="0"
Else: DateTimeArray(I)="0"
End if
End if
Next J
Next I
----
3. Compare each record to values selected on the search screen with respective array elements
For I = 1 to LastRow
If not UniqueNumberArray(I)= "0" then
If not SourceArray(I) = SelectedSource then UniqueNumberArray(I) = "0"
End if
If not UniqueNumberArray(I)= "0" then
If not TypeArray(I) = SelectedSource then UniqueNumberArray(I) = "0"
End if
Next I

4. Dump array back on to a blank sheet
For I = 1 to LastRow
If not UniqueNumberArray(I)= "0" then
Cells(I,1)=UniqueNumberArray(I)
Cells(I,2)=SourceArray(I)
Cells(I,3)=TypeArray(I)
End if
Next I

5. Load List box with result range
------------------------------------------------------
I know there are multiple For loops which is making the process slow...

I thought of sorting the array to ease the search... but, due to multiple arrays that cannot be possible...

If i make a single multidimentional array instead of multiple single dimentioal array, there is no way to sort a multidimentional array in place (quick sort, sorts only 1 dim array)

I cannot sort and delete rows on a worksheet (instead of arrays) because data from all 25 users exceeds 65000 lines

I need to bring this down to a max search wait time of 10 seconds
------------------------------------------------------
Any help is greatly appriciated....

First, the background - I am certain a code geek designed Newsgroup format
and sign-up. Take a lesson from Yahoo. (This should seal my fate with the
group.) OK, Debra, I'm here and on my feet, so far. I built my first userform
with Debra's concise tutorial a couple weeks ago. Excel is intimidating; I
lose my information input. Enough background.

The design-
A form to enter data, specifically related to wood properties - it's a hobby.
Form settings which are saved between sessions. The settings process the
data based on how it is obtained, three methods with one being customary.
Units of measurement are a saved setting depending on the country the form is
used in, US customary, SI, and a combo of both. This isn't just for me. Data
is computed in the form, and "committed" to the spreadsheet, or disposed of.

The spreadsheet displays all the entered data and the computed results,
buttoned in and safe from my electrical eraser - a mistake. This protected
information can then be further processed outside the safe area. (OK, I'm a
caveman; and the backup print will be available long after Excel 2003 fades
into history.)

The "Will it..." questions-
Resources: 1000 pages of j-walk "Power Programming with VBA"; The little
beginner's VBA book with monkeys on the cover. My VBA is in Office, however,
and attacks me with a splattering of multiple windows when I open it.

1. Can radio-button settings be saved between work sessions?

2. I'm sure the form and spreadsheet can be protected from "erasures", I
just have to wade through the security-type (policeman) terms in the
glossaries to find the term I need to know. (There is a question in this,
somewhere...)

3. Can either the form, or Excel, be locked at a certain point from
tampering? I have seen the big "CAUTION" boxes about passwords. The major
motivation for getting into Excel and VBA is that a more primitive form I
used was corrupted for personal gain.

4. Should I use something else?

I don't want to burn up a lot of time just to learn at some point in the
effort that a key element in the objective is not available. I haven't found
answers to the above questions; or don't understand enough to see the answers.

Sorry, this just could not be formatted to fit the 12 tips for posting to a
newsgroup.

Thanks. Your advice is appreciated.

--
Bruce McC

Hey all,

I have a program where the user selects a number in a cell based on a criteria. This number can be under a group (Baseball), and a sub group (Yankees). If a number, 2 (restricted by data validation), is selected for Baseball: Yankees, it puts Baseball in one column and Yankees in the next column to form a list that meets this criteria. If both Baseball and Yankees are in the same row, I don't want it to do anything. I run into trouble in my code if I have a list such as:
Basketball Yankees
Baseball Devil Rays
Basketball Cavaliers...

If I select the criteria 2 for Baseball Yankees, it does not add it to my list. That is because my code sees Basketball first, so it then searches for Yankees. Since it sees Yankees, it does nothing. I would like my code to say if there "Basketball" exists in column P, look in column Q in the same row where Basketball exists, and if Yankees exists in the column Q, do not move the values to columns P and Q.

If Target.Address
= "$H$9" Then
    Application.EnableEvents = False
    If Target.Value = "2" Then
        Set findit = Columns("P:P").Find(What:="*")
        If findit Is Nothing Then
            Cells(7, "P").Value = Cells(9, "G")
            Cells(7, "Q").Value = Cells(8, "B")
        Else
            Set findit = Columns("P:P").Find(What:="Basketball")
            If findit Is Nothing Then
                Cells(Rows.Count, "P").End(xlUp).Offset(1, 0).Value = Cells(9, "G")
                Cells(Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = Cells(8, "B")
            Else
                Set findit1 = Columns("Q:Q").Find(What:="Yankees")
                If findit1 Is Nothing Then
                    Cells(Rows.Count, "P").End(xlUp).Offset(1, 0).Value = Cells(9, "G")
                    Cells(Rows.Count, "Q").End(xlUp).Offset(1, 0).Value = Cells(8, "B")
            End If
        End If
End If
Is there possibly a way to incorporate that into the .Find(What:= field?

Thanks ahead of time,

Lucas

hello all...i think what i'm trying to do is quite simple yet i dno't know how to do it...so that's why i came to the experts!

i have a spreadsheet that has an enormous amount of information so i want to create a userform which will allow the user to enter in search criteria to find an order they are looking for.

for example....if they wanted to find all orders with a design pressure between 3000 and 4500 or a temperature equal to 550 or (and this is the one i'm really stuck on) search for the word "double" in the special features...and then the output should show the user what orders fall into their criteria

here is what my spreadsheet looks like (a condensed version)

Order Number pressure temperature Special features
050-33558 3300 100 double suction, no hydro
295-10581 2500 400 single suction
056-01200-01/02 5000 330 reduction gear

any help will be greatly appreciated!

THANKS!

Using code from http://www.excel-it.com/vba_examples.htm I am attempting to create a search code. I am using Excel from Office 2003

On a user form, the user types in a last name and clicks Find. I would like it to then search column A of sheet 12 (the actual range is row 2 through row 550) and populate a list box with records containing the name entered. When I press Find, I get the following error:

Method 'Range' of object '_Worksheet' failed, and the
 line of code is highlighted.  Can anyone tell me what I've got wrong?

Code:
Private Sub cmbFind_Click()
    Dim strFind, FirstAddress As String   'what to find
    Dim rSearch As Range  'range to search
    Set rSearch = Sheet12.Range("A2", Range("A550").End(xlUp))
    strFind = UserForm2.TextBox1.Value    'what to look for
    Dim f      As Integer
    With rSearch
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me
                
                f = 0
            End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f > 1 Then
                MsgBox "There are " & f & " instances of " & strFind
                Me.Height = frmMax
            End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With
End Sub


User Interface
New User Interface
The new results-oriented user interface makes it easy for you to work in Microsoft Office Excel. Commands and features that were often buried in complex menus and toolbars are now easier to find on task-oriented tabs that contain logical groups of commands and features. Many dialog boxes are replaced with dropdown galleries that display the available options, and descriptive tooltips or sample previews are provided to help you choose the right option.
No matter what activity you are performing in the new user interface, whether its formatting or analyzing data, Excel presents the tools that are most useful to successfully complete that task.
Introducing the new interface
There is a new look for Office Excel 2007, a new user interface (UI) that replaces menus, toolbars, and most of the task panes from previous versions of Excel with a single mechanism that is simple and apparent. The new user interface is designed to help you be more productive in Excel, more easily find the right features for various tasks, discover new functionality, and be more efficient.
The New Interface commands hierarchy:
1. Menu Tabs.
2. Ribbons.
3. Groups within each tab break a task into subtasks.
4. Command buttons (icons) in each group carry out a command or display a menu of commands.
Ribbon user interface: The primary replacement for menus and toolbars in Office Excel 2007 is the Ribbon. Designed for easy browsing, the Ribbon consists of tabs that are organized around specific scenarios or objects. The controls on each tab are further organized into several groups. The Ribbon can host richer content than menus and toolbars can, including buttons, galleries, and dialog box content.
Tabs that appear only when you need them: In addition to the standard set of tabs that you see on the Ribbon whenever you start Office Excel 2007, there are two other kinds of tabs, which appear in the interface and are useful for the type of task you are currently performing.
Contextual tools: Contextual tools enable you to work with an object that you select on the page, such as a table, a picture, or a drawing. When you click the object, the pertinent set of contextual tabs appears in an accent color next to the standard tabs.
Program tabs: Program tabs replace the standard set of tabs when you switch to certain authoring modes or views, including Print Preview.
File Button : This button is located in the upper-left corner of the Excel window and opens the menu shown here:

Quick Access Toolbar: The Quick Access Toolbar is located by default at the top of the Excel window and provides quick access to tools that you use frequently. You can customize the Quick Access Toolbar by adding commands to it.
Adding Commands to Quick Access Toolbar: In the Customize Quick Access Toolbar box, select either For all documents (as a default) or a specific document.
Click the command that you want to add, and then click Add.

Dialog Box Launchers: Dialog Box Launchers are small icons that appear in some groups. Clicking a Dialog Box Launcher opens a related dialog box or a task pane, providing more options related to that group.

Use the Keyboard to Access any Commands in the Ribbon
To use keyboard shortcut: To open a menu tab, press the Alt tab, now press a letter(s) or a number or a combination of a letter & a number , see below:
Step 1: press the Alt key or F10.
Step 2:
Press H, and then a letter(s) or a number or a combination of both (a letter & a number).
Or
Use the Tab key to move between command buttons in the Ribbon.
Memory management, Workbook, Worksheet & Cells
Memory Management
Memory management has been increased from 1 GB of memory in Microsoft Office Excel 2003 to 2 GB in Office Excel 2007.
You will also experience faster calculations in large, formula-intensive worksheets because Office Excel 2007 supports dual-processors and multithreaded chipsets.
Numbers of Rows, Columns & Cells in a Worksheet
Excel 2007 sheet contains 1,048,576 rows by 16,384 columns, total of 17,180,033,024 cells compare to previous Excel versions which hold 65,536 rows by 256 columns, total of 16,777,216 cells.
New file formats
XML-based file format: In 2007 Microsoft Office system, Microsoft is introducing new files formats for Word, Excel, and PowerPoint, known as the Microsoft Office Open XML formats. These new file formats facilitate integration with external data sources, and also offer reduced file sizes and improved data recovery. In Excel 2007, the default format for an Excel workbook is the Office Excel 2007 XML-based file format (.xlsx). Other available XML-based formats are the Excel 2007 XML-based and macro-enabled file format (.xlsm), the Excel 2007 file format for an Excel template (.xltx), and the Excel 2007 macro-enabled file format for an Excel template (.xltm).
Themes, Colors & Formatting
Office themes
In Office Excel 2007, you can quickly format the data in your worksheet by applying a theme and by using a specific style. Themes can be shared across other 2007 Office release applications, such as Microsoft Office Word and Microsoft Office PowerPoint, while styles are designed to change the format of Excel-specific items, such as Excel tables, charts, PivotTables, shapes, or diagrams.
Number of Colors
Excel 2007 supports up to 16 million colors.
Rich conditional formatting
You can implement and manage multiple Conditional Formatting rules that apply rich visual formatting in the form of gradient colors, data bars, and icon sets to data that meets those rules. Conditional formats are also easy to apply in just a few clicks, you can see relationships in your data that you can use for your analysis purposes.
Formulas & Functions
Easy formula writing
Resizable formula bar: The formula bar automatically resizes to accommodate long, complex formulas, which prevents the formulas from covering other data in your worksheet. You can also write longer formulas with more levels of nesting than you could in earlier versions of Excel.
Function AutoComplete: With Function AutoComplete, you can quickly write the proper formula syntax. From easily detecting the functions that you want to use, to getting help completing the formula arguments, you will be able to get formulas right the first time and every time.
Easy access to Named ranges: By using Name manager, you can organize, update, and manage multiple Named ranges in a central location, which helps all users who need to work on your worksheet interpret its formulas and data.
New Functions
Very important and useful functions are added to Excel 2007. The functions are IFERROR, AVERAGEIF, AVERAGEIFS, SUMIFS and COUNTIFS. Read more and see example in Chapter 9, page 155.
New OLAP formulas and cube functions
When you work with multidimensional databases (such as SQL Server Analysis Services) in Excel 2007, you can use OLAP formulas to build complex, free form, OLAP data bound reports. New cube functions are used to extract OLAP data (sets and values) from Analysis Services and display it in a cell. OLAP formulas can be generated when you convert PivotTable formulas to cell formulas or when you use AutoComplete for cube function arguments when you type formulas.
Charts
A New look of charts
You can use new charting tools to easily create professional-looking charts that communicate information effectively. Based on the theme that is applied to your workbook, the new, up-to-date look for charts includes special effects, such as 3-D, transparency, and soft shadows.
The new user interface makes it easy to explore the available chart types so that you can create the right chart for your data. Numerous predefined chart styles and layouts are provided so that you can quickly apply a good-looking format and include the details that you want in your chart.
Visual chart element pickers: Beside the quick layouts and quick formats, you can now use the new user interface to quickly change any element of the chart to best present your data. In a few clicks, you can add or remove titles, legends, data labels, trendlines, and other chart elements.
A modern look with OfficeArt: Since charts in Excel 2007 are drawn with OfficeArt, almost everything you can do to an OfficeArt shape can also be done to a chart and its elements. For example, you can add a soft shadow or a bevel effect to make an element to stand out or use transparency to make elements visible that are partially hidden in a chart layout. You can also use realistic 3-D effects.
Clear lines and fonts: Lines in charts appear less jagged, and ClearType fonts are used for text to improve readability.
More colors than ever: You can easily choose from the predefined theme colors and vary their color intensity. For more control, you can also add your own colors by choosing from 16 million colors in the Colors dialog box.
Chart templates: Saving your favorite charts as a chart template is much easier in the new user interface.
Shared charting
Using Excel charts in other applications: In Excel 2007, charting is shared between Excel, Word, and PowerPoint. Rather than using the charting features that are provided by Microsoft Graph, Word and PowerPoint now incorporate the powerful charting features of Excel. Since an Excel worksheet is used as the chart data sheet for Word and PowerPoint charts, shared charting provides the rich functionality of Excel, including the use of formulas, filtering, sorting, and the ability to link a chart to external data sources, such as Microsoft SQL Server and Analysis Services (OLAP), for up-to-date information in your chart. The Excel worksheet that contains the data of your chart can be stored in your Word document or PowerPoint presentation, or in a separate file to reduce the size of your documents.
Copying charts to other applications: Charts can be easily copied and pasted between documents or from one application to another. When you copy a chart from Excel to Word or PowerPoint, it automatically changes to match the Word document or PowerPoint presentation, but you can also retain the Excel chart format. The Excel worksheet data can be embedded in the Word document or PowerPoint presentation, but you can also leave it in the Excel source file.
Animating charts in PowerPoint: In PowerPoint, you can more easily use animation to emphasize data in an Excel-based chart. You can animate the entire chart or the legend entry and axis labels. In a column chart, you can even animate individual columns to better illustrate a specific point. Animation features are easier to find and give you much more control. For example, you can make changes to individual animation steps, and use more animation effects.
Sorting, Filtering & Tables
Improved sorting and filtering
You can now sort data by color and by more than 3 (and up to 64) levels. You can also filter data by color or by dates, display more than 1000 items in the AutoFilter dropdown list, select multiple items to filter, and filter data in PivotTables.
Excel table enhancements
You can use the new user interface to quickly create, format, and expand an Excel table (known as an Excel list in Excel 2003) to organize the data on your worksheet so that its much easier to work with.
PivotTables
Easy-to-use PivotTables
By using the new PivotTable user interface, the information that you want to view about your data is just a few clicks away. You no longer have to drag data to drop zones that arent always an easy target. Instead, you can simply select the fields that you want to see in a new PivotTable field list.
After you create a PivotTable, you can take advantage of many other new or improved features to summarize, analyze, and format your PivotTable data.
Sharing & Connections
New ways to share your work
Using Excel Services to share your work: If you have access to Excel Services, you can use it to share your Office Excel 2007 worksheet data with other users, such as executives and other stakeholders in your organization. In Excel 2007, you can save a workbook to Excel Services and specify the worksheet data that you want other people to see. In a browser (browser: Software that interprets HTML files, formats them into Web pages, and displays them. A Web browser, such as Microsoft Internet Explorer, can follow hyperlinks, transfer files, and play sound or video files that are embedded in Web pages.), they can then use Microsoft Office Excel Web Access to view, analyze, print, and extract this worksheet data. They can also create a static snapshot of the data at regular intervals or on demand. Excel Web Access makes it easy to perform activities, such as scrolling, filtering, sorting, viewing charts, and using drill-down in PivotTables. You can also connect the Excel Web Access Web Part to other Web Parts to display data in alternative ways. And with the right permissions, Excel Web Access users can open a workbook in Excel 2007 so that they can use the full power of Excel to analyze and work with the data on their own computers if they have Excel installed.
Using this method to share your work ensures that other users have access to one version of the data in one location, which you can keep current with the latest details. If you need other users, such as team members, to supply you with comments and updated information, you may want to share a workbook the same way.
Quick connections to external data
You no longer need to know the server or database names of corporate data sources. Instead, you can use Quick Launch to select from a list of data sources that your administrator or workgroup expert has made available for you. A connection manager in Excel allows you to view all connections in a workbook and make it easier to reuse a connection or to substitute a connection with another user.
Printing
Better printing experience
Page Layout View: In addition to the Normal view and Page Break Preview view, Excel 2007 provides a Page Layout View. You can use this view to create a worksheet while keeping an eye on how it will look in printed format. In this view, you can work with page headers, footers, and margin settings right in the worksheet, and place objects, such as charts or shapes, exactly where you want them to be. You also have easy access to all page setup options on the Page Layout tab in the new user interface so that you can quickly specify options, such as page orientation. Its easy to see what will be printed on every page, which will help you avoid multiple printing attempts and truncated data in printouts.
Saving to PDF and XPS format: Like other 2007 Office release applications, Excel 2007 supports saving a workbook to a high-fidelity fixed file format, such as Portable Document Format (PDF) or XML Paper Specification (XPS) format, that encapsulates how it will look when it is printed. This allows you to share the content of your workbook in a format that is easy for other people to view online or print, without including the underlying formulas, external data queries, or comments.
Actually, this is what you did in earlier versions of Excel to collect the information you need before you save it to Excel Services.
Using Document Management Server: Excel Services can be integrated with Document Management Server to create a validation process around new Excel reports and workbook calculation workflow actions, such as a cell-based notification or a workflow process based on a complex Excel calculation. You can also use Document Management Server to schedule nightly recalculation of a complex workbook model.


No luck finding an answer? You could always try Google.