VBA and Excel listboxes

vavs

Well-known Member
Joined
Jul 2, 2004
Messages
514
I am trying to set up a user interface for my gym. The director has an excel spreadsheet and he would like the user to update the sheet. The goal is to have the user see a screen where a drop down list provides all of the gym members. The user would select their name and hit a button. This would add an "X" to the column associated to the date.

For example if the user logged in on Monday September 3, when they hit the button, an "X" would appear in the column 9/3 and the row with the users name.

I can create the form, but I am not sure how to load the list box with the date from the column with the names.

Also, is it better to determine the date up front and select the column as a constant. On startup determine what the date is and select the appropriate column.

Thanks in advance for your help. This is the forum with greatest wealth of Excel knowledge on the web.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
HI

This assumes that your names are in column A starting in A2, and the dates are in row 1 starting in B1 (1/9/06, 2/9/06....)

The form has a listbox (listbox1) that has the names from column A. There is a button that will put in the result when a name is selected. The code for the button

Code:
Private Sub CommandButton1_Click()
  roww = WorksheetFunction.Match(ListBox1.Value, Range("A:A"), 0)
  coll = Range("1:1").Find(what:=Date).Column
  Cells(roww, coll).Value = "X"
End Sub

HTH

Tony
 

vavs

Well-known Member
Joined
Jul 2, 2004
Messages
514
Tony,

I tried that and was unsuccessful. Let me alter my question so that it may make things clearer.

I want to have a form where the information which appears on the screen includes only two items. A list box with all of the gym patrons and a button to say they have logged in.

The list box would allow the user to select their name. When they select the name and hit the button, the spreadsheet is updated for that particular patron and date. From there I will be able to determine how many people have showed up on any day and how often each person has shown up for any period of time.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

That is what I have given you. A form that has a listbox which takes its input from the list of names in column A, starting in A2. The form will already be loaded (fired off when the spreadsheet is loaded perhaps). The button will mark the relevant entry when pressed.


Tony
 

Forum statistics

Threads
1,141,015
Messages
5,703,746
Members
421,312
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top