How do I create a button that will go to a certain worksheet based on user input?

[mohr96]

Hello,

I am creating a program that involves a page where the user will input data, and the program will then perform the calculation the data and take the user to a final output page. The data that the user will be inputting will also contain parameters, and depending on how many parameters the user has, a different calculation will be performed. With this, I set up two separate final output pages, one for if the user's data has only one parameter, and one where the user has more than one parameter. So far I have been able to create two separate buttons that the user may select, depending on how many parameters they have, however, to make it easier on the user, I want to know if there is a way to make only one button, and however many parameters the user types in, that button will go to the correct worksheet based on a macro. Here is a visual to show what I am looking for:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Parameter Names:[/TD]
[TD]Force[/TD]
[TD](empty)[/TD]
[TD](empty)[/TD]
[/TR]
</tbody>[/TABLE]
When the user pushes a button, it should take them to the one parameter calculation page.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Parameter Names:[/TD]
[TD]Force[/TD]
[TD]Speed[/TD]
[TD](empty)[/TD]
[/TR]
</tbody>[/TABLE]
When the user pushes the same button, it should take them to the multiple parameter calculation page.

So to summarize, assuming, the cell containing parameter names is cell A1, how would I be able to create a single button that will take the user to a different worksheet based on how many parameters the user types into the program? Please let me know.

 

[Jeffrey Mahoney]

Hope this gets you going in the right direction.

Private Sub CommandButton21_Click()  Dim Count As Long
  
  If Len(Range("B2").Value) > 0 Then Count = 1
  If Len(Range("C2").Value) > 0 Then Count = Count + 1
  If Len(Range("D2").Value) > 0 Then Count = Count + 1
  
  If Count = 1 Then
    Sheets("SingleParm").Activate
  ElseIf Count > 1 Then
    Sheets("MultiParm").Activate
  End If
  
End Sub

 

[mohr96]

This worked! Thanks. To expand on this question though, what if instead of the (empty) cells being empty, what if they all had a label inside of them such as "[Name]"? In other words, the program should accept any input in the cell besides the text "[Name]". Visually, it would look like this,

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Parameters:[/TD]
[TD]Force [/TD]
[TD][Name][/TD]
[TD][Name][/TD]
[/TR]
</tbody>[/TABLE]

Basically the program is performing the same function, except this time it will not count any cell that has "[Name]" inside of it. I just am not sure of the wording of VBA. Please let me know. Thanks!

 

[Jeffrey Mahoney]

Since I have a minute. Range("B2").Value gives you the contents of cell B2. If B2 has a formula, this will give you the result of the formula. Range("B2").Formula gives you the string of text that makes up the formula, not the result. If cell B2 is not a formula, it still returns the contents. What you do with the string is up to you. The VBA function "Len" just evaluates the length of the string. I could have easily used this evaluation and it would have worked the same: If Range("B2").value <> "". In the second CODE example, I used a loop to check multiple cells. I defined the range and used a For Each Loop to evaluate each cell. I prefer this method even for a small number of cells. Hopefully breaking down how each command works will help you logically edit your own code.

Cheers


'This code checks those cells for the string [Name].

Private Sub CommandButton21_Click()  Dim Count As Long
  
  If Len(Range("B2").Value) > 0 And Range("B2").Value <> "[Name]" Then Count = 1
  If Len(Range("C2").Value) > 0 And Range("C2").Value <> "[Name]" Then Count = Count + 1
  If Len(Range("D2").Value) > 0 And Range("D2").Value <> "[Name]" Then Count = Count + 1
  
  If Count = 1 Then
    Sheets("SingleParm").Activate
  ElseIf Count > 1 Then
    Sheets("MultiParm").Activate
  End If
  
End Sub

'This code will also check for [Name]

Private Sub CommandButton22_Click()  Dim Count As Long
  Dim Rng As Range
  Dim Cel As Range
  
  Set Rng = Range("B2:D2")
  For Each Cel In Rng
    If Cel.Value <> "[Name]" And Cel.Value <> "" Then Count = Count + 1
  Next Cel
  
  If Count = 1 Then
    Sheets("SingleParm").Activate
  ElseIf Count > 1 Then
    Sheets("MultiParm").Activate
  End If
  
End Sub

 

[mohr96]

Thank you very much! It works!