VBA Code: How to select a sheet from a user inputbox

OhMyGoodness

New Member
Joined
Nov 8, 2022
Messages
1
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hey I am having trouble coding this vba code. I need the code to ask the user if they want data or a graph. If the user selects Yes From there, I need to the code to look at the selected input and see if that input is a valid sheet name. If not, the input box will display again until valid sheet name. If the sheet is valid then I need the sheet to be selected or show up whenever the user enters a valid value. I hope that makes sense.

For example, if the user enters (10-1) that is a valid sheet or (1-1) valid sheet but if it is (14-1) or (a-a) that is not a valid sheet and the input is re-ran again.

Note I have not gotten to the graphing part yet so do not worry about if the user selects no yet.

Code:

Sub InputValidation()
Dim str As String
Dim inp As String
Dim sht As Worksheet


str = MsgBox("Do you want to select a dataset (Yes) or a Graph (No)", vbQuestion + vbYesNo)

If str = vbYes Then
inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
If StrPtr(inp) = 0 Then
If MsgBox("Do you really want to QUIT", vbYesNo + vbQuestion) = vbYes Then MsgBox "Thank You Goodbye"
Exit Sub
End If
ElseIf inp = "#-#" Or "##-#" Then
If Sheets(sht).Name = inp Then
Worksheets(inp).Select
End If
Else
MsgBox "This load and test cannot be found"

If str = vbNo Then
inp = InputBox("Please enter a load value (10 or a load and trial (10-1)")
If StrPtr(inp) = 0 Then
If MsgBox("Do you really want to QUIT", vbYesNo + vbQuestion) = vbYes Then MsgBox "Thank You Goodbye"
Exit Sub
End If
End If
End If

End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Why not use a userform with a listbox and list the sheets in it then user picks & clicks (or double clicks)? Prompting over and over for a valid name seems odd. Or maybe I read that wrong.
When you post code, best that you use vba code tags (vba button on posting toolbar) so as to maintain indentation and readability.
 
Upvote 0

Forum statistics

Threads
1,215,639
Messages
6,125,970
Members
449,276
Latest member
surendra75

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
Back
Top