User Input

[Jon Mulkey]

I want to make a Dashboard that uses a userform or something. I want the form to have dependent cells. For instance: I am writing a spreadsheet to track sporting events (football and basketball, etc) the sports are on 8 separate worksheets with tables that are to be populated automatically based on the user input. In the dashboard they would first select the sport (NFL, NCAAF, NBA, etc.) once the sport is selected they will select a team which would be dependent on the sport. For instance if they choose NFL for the sport then the team box would automatically give them a drop down of the NFL teams. I used this in a dependent cell with data validation that is the way I would like it to work in a userform. Then what ever they select it would populate the next line down in the table on the correlating worksheet. If they choose NFL it would populate the next line of the NFL worksheet table, if they choose NBA it would populate the next row in the table on the NBA worksheet and so on. If it is not possible in a userform what process should I use to accomplish this?

Thanks in advance for your help

 

[ranman256]

yes you can use userform.
a combo box to pick the sport (this would refill cboTeams)

another combo box to pick the team.

fill out data fields, click a Save button,
then the code would post the data,,

sub btnSave_click()
sheets(cboSport).activate
range("a1").select
FindNextFreeRec
activecell.value = txtBox1
'etc...
end sub

Public Sub FindNextFreeRec()
Range("A1").Select
Select Case True
   Case ActiveCell.Value = ""
   Case ActiveCell(1, 0).Value = ""
        NextRow
   Case Else
        FarDown
        NextRow
End Select
End Sub

Private Sub NextRow()
ActiveCell.Offset(1, 0).Select
End Sub


Sub FarDown()
    Selection.End(xlDown).Select
End Sub

 

[Jon Mulkey]

yes you can use userform.
a combo box to pick the sport (this would refill cboTeams)

another combo box to pick the team.

fill out data fields, click a Save button,
then the code would post the data,,

sub btnSave_click()
sheets(cboSport).activate
range("a1").select
FindNextFreeRec
activecell.value = txtBox1
'etc...
end sub

Public Sub FindNextFreeRec()
Range("A1").Select
Select Case True
   Case ActiveCell.Value = ""
   Case ActiveCell(1, 0).Value = ""
        NextRow
   Case Else
        FarDown
        NextRow
End Select
End Sub

Private Sub NextRow()
ActiveCell.Offset(1, 0).Select
End Sub


Sub FarDown()
    Selection.End(xlDown).Select
End Sub

Thank you that is a good start, This one only deals with one sheet though. I need it to automatically determine which of the 8 sheets it needs to go to . Ex: if they pick NFL then when they hit save it needs to go to the NFL sheet, If they pick NBA I need it to go to the NBA sheet like it needs to have some kind of "IF" statement in it for those conditions. any ideas on that?

 

[ranman256]

if 1 sheet is 1 sport, how do you store teams?
all teams in 1 sheet? how do you separate?

 

[Jon Mulkey]

if 1 sheet is 1 sport, how do you store teams?
all teams in 1 sheet? how do you separate?

The process is that they choose a sport (ie: nfl) this is done by using data validation and a named dynamic range called "sports" all of the named ranges for use with the data validation are on a hidden worksheet called data. It includes the team names for each sport etc.. so they pick the sport then in the next cell using list in data validation and the indirect command referencing the cell where they choose the sport the team names for that sport populate the drop down.

I have images of this on a flickr account but can't seem to drop them in this message so here is the photostream if that helps https://www.flickr.com/photos/138743170@N08/

thanks for your help