Posts by longtimeflyer

longtimeflyer · Jun 27th 2022

I host multiple seminars per year. Each event is attended by 200-300 people.

All attendee information is stored on one spreadsheet. I have created a user form that the administrator can use to assign a table number to an attendee.

I would like to create pivot tables that just contain first and last name that automatically populate when a table number is assigned to an attendee. However, the issue I am facing is at the time I create the pivot tables no one will be assigned to any of the tables. From what I know, it is not possible to have a pivot table that does not hold any data.

Can anyone suggest a workaround to this problem or maybe suggest an entirely different approach to displaying all seating information on one spreadsheet?

TIA.

longtimeflyer · Apr 19th 2022

Rory -

Your suggestion fixed the issue! Unfortunately, it appears to have caused a different error: when I attempt to edit and save a record I get the following error message, "Compile Error: Sub or Function Not Defined".

The error occurs on Line 22

Code

Private Sub cmdEdit_Click()

'declare the variables
    Dim findvalue As Range
    'error handling
    On Error GoTo errHandler:
    'check for values
    If Reg1.Value = "" Or Reg2.Value = "" Then
        MsgBox "There is not data to edit"
        Exit Sub
    End If
    'edit the row
    Set findvalue = Sheet2.Range("F:F").Find(What:=Reg4, LookIn:=xlValues).Offset(0, -3)
    'if the edit is a name then add it
    Me.Reg3.Value = Me.Reg1.Value + " " + Me.Reg2.Value
    
    For X = 1 To cNum
        findvalue = Me.Controls("Reg" & X).Value
        Set findvalue = findvalue.Offset(0, 1)
    Next
    'refresh the listbox
    Lookup
    'error block
    On Error GoTo 0
    Exit Sub
errHandler:
    MsgBox "An Error has Occurred  " & vbCrLf & _
           "The error number is:  " & Err.Number & vbCrLf & _
           Err.Description & vbCrLf & "Please notify the administrator"

End Sub

Display More

longtimeflyer · Apr 19th 2022

I recently found sample code online for a Student Registration Userform that I downloaded and modified for my needs. I was able to modify everything successfully to meet my needs with the exception of the "Search" function.

To use the search function the user must enter a search term, such as a student's last name, click the "Search" button and all records matching the search term will be displayed in the corresponding listbox. However, this process generates the following error message, "Compile Error: Invalid Use Of Me Keyword". I have tried modifying the code in several ways, but always end up with the same error message.

Any suggestions regarding correcting the code or even deleting the functionality the offending code references would be appreciated. See code below.

"Me Error" is Line 31

Code

Sub Lookup()
'declare the variables
    Dim rngFind As Range
    Dim strFirstFind As String
    'error statement
    On Error GoTo errHandler:
    'clear the listbox
    lstLookup.Clear
    'look up parts or all of full mname
    With Sheet2.Range("E:E")
        Set rngFind = .Find(txtLookup.Text, LookIn:=xlValues, lookat:=xlPart)
        'if value found then set a variable for the address
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
            'add the values to the listbox
            Do
                If rngFind.Row > 1 Then
                    lstLookup.AddItem rngFind.Value
                    lstLookup.List(lstLookup.ListCount - 1, 1) = rngFind.Offset(0, 1)
                    lstLookup.List(lstLookup.ListCount - 1, 2) = rngFind.Offset(0, 2)
                    lstLookup.List(lstLookup.ListCount - 1, 3) = rngFind.Offset(0, 3)
                    lstLookup.List(lstLookup.ListCount - 1, 4) = rngFind.Offset(0, 4)
                    lstLookup.List(lstLookup.ListCount - 1, 5) = rngFind.Offset(0, 5)
                End If
                'find the next address to add
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With
    'disable payroll editing
    Me.Reg4.Enabled = False
    Me.cmdEdit.Enabled = False
    'error block
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"
End Sub

Display More

longtimeflyer · Jan 5th 2022

Guest_List is the sheet name where all the data will stored. Sheet # is 10

longtimeflyer · Jan 4th 2022

I am building a userform to track seminar invitation responses. Amongst other things, the form will contain three option buttons; "Accept, Decline, No Response". However, I am having issues with the coding and cannot find any online resources to help me.

There will possibly as many as 500 records that will be saved to a spreadsheet. I *think* my main issue is with the Range always pointing to C3.

It would be great if anyone has any suggestions or can point me to a good online resource.

TIA

Here is the code I have so far:

Code

Private Sub optOptionButton1_Click()


If Userform1.optOptionButton1.Value = True Then


Guest_List.Range("C3") = "Accept"


End If


End Sub


Private Sub optOptionButton2_Click()


If Userform1.optOptionButton1.Value = True Then


Guest_List.Range("C3") = "Decline"


End If


End Sub


Private Sub optOptionButton3_Click()


If Userform1.optOptionButton1.Value = True Then


Guest_List.Range("C3") = "No Response"


End If


End Sub

Display More

longtimeflyer · Dec 22nd 2021

This was exactly what I was looking for. THANK YOU!

longtimeflyer · Dec 21st 2021

I want to save the data to a sheet called "database", location A10

Thanks.

longtimeflyer · Dec 21st 2021

I have, what I think is a fairly simple question:

I have created a userform with 6 textboxes and one combobox. At the moment everything works perfectly, however, I would like to change the location the data is saved to.

At the moment, the data is saved to the first available cell in the upper right hand corner of the spreadsheet. However, I would like to save the data to a specific location on a different hidden worksheet. The purpose of this is to prevent access to the data table by the end user.

Hopefully, someone has an easy answer to this question. I have included the relevant code below

TIA

Code

Private Sub CommandButton1_Click()

Dim A As Long

A = Cells(Rows.Count, 1).End(xlUp).Row + 1

Cells(A, 1).Value = FNameva.Value

Cells(A, 2).Value = LNameva.Value

Cells(A, 3).Value = Cellva.Value

Cells(A, 4).Value = emailva.Value

Cells(A, 5).Value = Streetva.Value

Cells(A, 6).Value = Cityva.Value

Cells(A, 7).Value = Stateva.Value

Cells(A, 8).Value = Zipva.Value

Cells(A, 9).Value = Rolecmb.Value

FNameva.Value = ""

LNameva.Value = ""

Cellva.Value = ""

emailva.Value = ""

Streetva.Value = ""

Cityva.Value = ""

Stateva.Value = ""

Zipva.Value = ""

Rolecmb.Value = ""

End Sub

Display More

Posts by longtimeflyer

Create A Pivot Table That Initially Contains No Data

Compile Error: Invalid Use Of Me Keyword

Compile Error: Invalid Use Of Me Keyword

Userform Option Buttons

Userform Option Buttons

Saving Userform Data

Saving Userform Data

Saving Userform Data