VBA and Excel listboxes

[vavs]

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.

 

[acw]

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

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]

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]

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