Input box select sheet

[Tigerexcel]

I'm trying to insert an input box whereby the user just selects a worksheet, rather than having to type it in.
So far I have,

Dim Sheetname As String

If ActiveWorkbook Is Nothing Then Exit Sub


Sheetname = Application.InputBox("Where is the source data?")

Sheets(Sheetname).Select


When you select the worksheet, it will write it in with exclamation mark eg =Attendance!, the macro then returns an error message asking me to check my formulae, so it is a range issue.
However if I simply type Attendance in the input box, it works fine., if I select =Attendance! and remove the equal sign and the exclamation mark, it works fine. How can I amend the code so that the user simply just clicks onto the required worksheet?

 

[Jaafar Tribak]

Beautiful, just beautiful.
I went back to the start and created a new workbook from scratch with your code.
I ran it and it worked perfectly. It gave me the expected messages and also worked for Esc in the Popup and Esc and Cancel in the Listbox.
Then my big challenge, incorporate my code into yours to get it to copy sheets in the way I want.
It couldn't be as simple as replace the message box with my code, surely? With a couple of extra variable declarations, yes, yes it was.
Thanks a million for your help on this. I had been looking for this for some time.

Stay safe in these troubled times.

Glad it worked for you and thanks for the feedback .

 

[Jaafar Tribak]

@Tigerexcel
Here is the workbook demo (Selecting sheet via InputBox)

@sparky2205
And this is the workbook demo for handling the ESC key in the Popup and ESC,, Cancel and X close in the Listbox (for future reference)

Regards.

 

[Tigerexcel]

@Tigerexcel
Here is the workbook demo (Selecting sheet via InputBox)

@sparky2205
And this is the workbook demo for handling the ESC key in the Popup and ESC,, Cancel and X close in the Listbox (for future reference)

Regards.

Thanks Jaafar, how do I access the file?

 

[sparky2205]

@Tigerexcel
Here is the workbook demo (Selecting sheet via InputBox)

@sparky2205
And this is the workbook demo for handling the ESC key in the Popup and ESC,, Cancel and X close in the Listbox (for future reference)

Regards.

Thanks Jaafar,
I'll keep that tucked away for reference.

Just in case you missed my question from my last post:
One question I forgot.
Why will screenupdating not work in the Sub SelectSheet()?
It gives a variable not defined error.
No biggie, but I usually include this if there's a lot going on in the background.

@Tigerexcel
I was able to click on the link in Jaafar's post and click on the download button then save it from there.

 

[Jaafar Tribak]

One question I forgot.
Why will screenupdating not work in the Sub SelectSheet()?
It gives a variable not defined error.
No biggie, but I usually include this if there's a lot going on in the background.

Can you post a small code example that illustrates the problem ?

 

[sparky2205]

Sub SelectSheet()

    Dim ws As Worksheet
Dim sName As String
Dim sInterimName As String
Dim iCountName As Integer
Dim pw As String

pw = ""
iCountName = 0

ScreenUpdating = False

Call ShowSheetsPopUp
Set ws = Worksheets(ActiveSheet.Name)
sName = ActiveSheet.Name

If GetAsyncKeyState(VBA.vbKeyEscape) = 0 Then
If bCancelled = False Then
'Unprotect the workbook
ThisWorkbook.Unprotect Password:=pw
'Copy the worksheet
Sheets(sName).Copy after:=Sheets(sName)
sInterimName = ActiveSheet.Name
'Reprotect the workbook
ThisWorkbook.Protect Password:=pw, structure:=True
'Protect the new worksheet
Worksheets(sInterimName).Protect Password:=pw, _
userinterfaceonly:=True, _
AllowFiltering:=True, _
AllowFormattingCells:=True, _
AllowFormattingRows:=True, _
AllowFormattingColumns:=True, _
AllowInsertingRows:=True
Exit Sub
End If
End If

ScreenUpdating = True

End Sub

Apologies but my code always loses its indents when I post it here

 

[Jaafar Tribak]

You need to fully qualify the ScreenUpdating Property otherwise the vba compiler thinks it is variable which has not been defined since you have the Option Explicit statement at the top of the module.
Application.ScreenUpdating = True
Application.ScreenUpdating = False

 

[sparky2205]

Now I feel foolish.
I've use it so many times and I didn't cop that I'd left out the Application. piece.

Thanks again Jaafar and have a good day.

 

[sparky2205]

Hi Jaffar,
Because of your excellent efforts with this issue I wanted to bring your attention to another issue I have had in the past, which went without resolution. I'm aware of the rules around high jacking threads but I didn't know how to direct this to you any other way. I will just post a link here to it:

Spell check unlocked cells with highlighting

Hi folks, this is one of those ones where there is a lot of chatter out there about it but I haven't been able to get a fix for my issue. My issue: I want a macro that performs a spell check on the unlocked cells only on a protected sheet while highlighting the cell where the error occurs. What...

www.mrexcel.com

It's an issue that I am unable to resolve but which would be of great benefit to me if I could get it sorted.
And no need to worry I won't be addressing all my Excel issues to you personally in the future. This is one of those rare ones that a few have had a go at but a resolution remains elusive. If you could have a look I'd really appreciate it.