UserForm and macro woes

R

Robert_L_Ross

Ok, here's where I'm at.

I have a document template with a userform with three check boxes and a drop
down. When the form loads, the code has it go to a spreadsheet and return
all schools from column 1 ('schools') of a 4 column table.

I want the users to be able to 'filter' the list returned by clicking one or
more of the checkboxes. When the user clicks checkbox1 (STAFFcheckbox) to
True, I want the list to 'filter' to only those schools.

I have the code working, and I've set it up so that it checks the status of
all three check boxes (so I can use one code when any one of the three
buttons are changed).

Problem is, I should have this code in a module and not tied to each of the
three buttons (making three copies of the code). I can't seem to get the
buttonclick action to run the macro.

Here's my code on the userform:
Sub autonew()

Load UserForm1

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Open the database
Set db =
opendatabase("G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST.xls", False,
False, "Excel 8.0")

' Retrieve the recordset
'Set rs = db.OpenRecordset("SELECT * FROM `schools` WHERE SCHOOLS <> ''")
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
UserForm1.ComboBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
UserForm1.ComboBox1.Column = rs.GetRows(NoOfRecords)

'Me.Repaint

' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

UserForm1.Show

End Sub

So when the form is new, I get my userform just fine.
Now, let's set a checkbox:
Sub UPDATELIST()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
Dim CONDITION As Integer

' Open the database
Set db =
opendatabase("G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST_NEW.xls", False,
False, "Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")

'Stafford Only
If STAFFCheckBox = True And PLUSCheckBox = False And GRADCheckBox =
False Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND STAFF = 'X'")
End If

'PLUS Only
If STAFFCheckBox = False And PLUSCheckBox = True And GRADCheckBox =
False Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND PLUS = 'X'")
End If

'GRAD PLUS Only
If STAFFCheckBox = False And PLUSCheckBox = False And GRADCheckBox =
True Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND GPLUS = 'X'")
End If

'Stafford/PLUS
If STAFFCheckBox = True And PLUSCheckBox = True And GRADCheckBox = False
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND STAFFORD = 'X' and PLUS = 'X'")
End If

'PLUS/GRAD PLUS
If STAFFCheckBox = False And PLUSCheckBox = True And GRADCheckBox = True
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND PLUS = 'X' and GPLUS = 'X'")
End If

'Stafford/GRAD PLUS
If STAFFCheckBox = True And PLUSCheckBox = False And GRADCheckBox = True
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND STAFFORD = 'X' and GPLUS = 'X'")
End If

'Stafford/Plus/GRAD PLUS
If STAFFCheckBox = True And PLUSCheckBox = True And GRADCheckBox = True
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")
End If

'NONE
If STAFFCheckBox = False And PLUSCheckBox = False And GRADCheckBox =
False Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")
End If

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
UserForm1.ComboBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
UserForm1.ComboBox1.Column = rs.GetRows(NoOfRecords)

'Me.Repaint

' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

Now, I can put that code in each button click, but that would be 3 areas I'd
have to update if our source list changes (adds columns, etc.).

I want have the userform's checkboxclick action run this code:
Private Sub GRADCheckBox_Click()
Application.Run (UPDATELIST)
End Sub

Private Sub PLUSCheckBox_Click()
Application.Run (UPDATELIST)
End Sub

Private Sub STAFFCheckBox_Click()
Application.Run (UPDATELIST)
End Sub

When I put the code in this way, nothing happens when I click a check box,
but the code runs when it's in each checkbox click action.

What am I doing wrong?

Thanks!
 
R

Russ

See Word VBA Help for: call
Ok, here's where I'm at.

I have a document template with a userform with three check boxes and a drop
down. When the form loads, the code has it go to a spreadsheet and return
all schools from column 1 ('schools') of a 4 column table.

I want the users to be able to 'filter' the list returned by clicking one or
more of the checkboxes. When the user clicks checkbox1 (STAFFcheckbox) to
True, I want the list to 'filter' to only those schools.

I have the code working, and I've set it up so that it checks the status of
all three check boxes (so I can use one code when any one of the three
buttons are changed).

Problem is, I should have this code in a module and not tied to each of the
three buttons (making three copies of the code). I can't seem to get the
buttonclick action to run the macro.

Here's my code on the userform:
Sub autonew()

Load UserForm1

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' Open the database
Set db =
opendatabase("G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST.xls", False,
False, "Excel 8.0")

' Retrieve the recordset
'Set rs = db.OpenRecordset("SELECT * FROM `schools` WHERE SCHOOLS <> ''")
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
UserForm1.ComboBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
UserForm1.ComboBox1.Column = rs.GetRows(NoOfRecords)

'Me.Repaint

' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

UserForm1.Show

End Sub

So when the form is new, I get my userform just fine.
Now, let's set a checkbox:
Sub UPDATELIST()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
Dim CONDITION As Integer

' Open the database
Set db =
opendatabase("G:\GS-220\SLATE\CORRECTION_SHEETS\SCHOOL_LIST_NEW.xls", False,
False, "Excel 8.0")

' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")

'Stafford Only
If STAFFCheckBox = True And PLUSCheckBox = False And GRADCheckBox =
False Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND STAFF = 'X'")
End If

'PLUS Only
If STAFFCheckBox = False And PLUSCheckBox = True And GRADCheckBox =
False Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND PLUS = 'X'")
End If

'GRAD PLUS Only
If STAFFCheckBox = False And PLUSCheckBox = False And GRADCheckBox =
True Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND GPLUS = 'X'")
End If

'Stafford/PLUS
If STAFFCheckBox = True And PLUSCheckBox = True And GRADCheckBox = False
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND STAFFORD = 'X' and PLUS = 'X'")
End If

'PLUS/GRAD PLUS
If STAFFCheckBox = False And PLUSCheckBox = True And GRADCheckBox = True
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND PLUS = 'X' and GPLUS = 'X'")
End If

'Stafford/GRAD PLUS
If STAFFCheckBox = True And PLUSCheckBox = False And GRADCheckBox = True
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> '' AND STAFFORD = 'X' and GPLUS = 'X'")
End If

'Stafford/Plus/GRAD PLUS
If STAFFCheckBox = True And PLUSCheckBox = True And GRADCheckBox = True
Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")
End If

'NONE
If STAFFCheckBox = False And PLUSCheckBox = False And GRADCheckBox =
False Then
Set rs = db.OpenRecordset("SELECT Schools FROM `NEWSCHOOLLIST` WHERE
Schools <> ''")
End If

' Determine the number of retrieved records
With rs
.MoveLast
NoOfRecords = .RecordCount
.MoveFirst
End With

' Set the number of Columns = number of Fields in recordset
UserForm1.ComboBox1.ColumnCount = rs.Fields.Count

' Load the ListBox with the retrieved records
UserForm1.ComboBox1.Column = rs.GetRows(NoOfRecords)

'Me.Repaint

' Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

End Sub

Now, I can put that code in each button click, but that would be 3 areas I'd
have to update if our source list changes (adds columns, etc.).

I want have the userform's checkboxclick action run this code:
Private Sub GRADCheckBox_Click()
Application.Run (UPDATELIST)
End Sub

Private Sub PLUSCheckBox_Click()
Application.Run (UPDATELIST)
End Sub

Private Sub STAFFCheckBox_Click()
Application.Run (UPDATELIST)
End Sub

When I put the code in this way, nothing happens when I click a check box,
but the code runs when it's in each checkbox click action.

What am I doing wrong?

Thanks!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
VBA word change keys 0
Making a macro only work on certain files 2
how to populate textbox based on combobox in word from excel 0
Change a great number of words in word files, with a macro as short as possible. 5
Loop through pages and insert header image 0
Inserting text at a bookmark with a Userform 0

Top