Use of InputBox doesn't succeed in getting sheet name

**ShawnW** · 07-05-2013, 10:09 PM

I'm asking the user to identify source and destination worksheets for some data movement.

I don't understand why this won't work (and have tried many less sensical alternatives!). When I run the function, if I navigate to another tab to select a range in response to the prompt (basically to ID the worksheet), ActiveSheet.name still is the sheet that I was on when I began running the function.

Please Login or Register  to view this content.

**Ron Coderre** · 07-05-2013, 10:19 PM

I think it would be more practical to create a user form that contains combo boxes that list worksheet names in the active workbook and have the user select from those.

Here's some sample code that loads combobox1 with the sheet names:

Please Login or Register  to view this content.

b

Is that something you can work with?

**Leith Ross** · 07-05-2013, 10:23 PM

Hello ShawnW,

You have ActiveSheet.Name assigned to GetDest. The code should be...

Please Login or Register  to view this content.

**ShawnW** · 07-06-2013, 12:59 AM

Thanks Leith, that works. I guess it was weird I wasn't using DestRange. Was just sort of setting that in the process of getting them to select a cell, which I thought would make ActiveSheet what I wanted it to be. Anyway, it works now.

Ron, after doing some googling and not finding anything simple enough, and getting progressively less effective with tiredness setting in...I am not able to get a combobox working. (I don't know how to call it, etc.) If it's a simple explanation, I'd love to hear it, but I think I just might need more of an overall tutorial on it and I don't want to waste too much of your time.

**Ron Coderre** · 07-06-2013, 09:56 AM

Instead of an explanation...how about a working example?

I attached a workbook that contains VBA code.

ListWkbkSheets_v1a.xlsm

Click the button on Sheet1 to view a userform that lists the sheets of that workbook.
Click OK to see your selection.
Click Cancel to .... well cancel.

Change the OK button code to suit your needs.

I hope that helps.

**ShawnW** · 07-08-2013, 09:52 AM

Thanks for the extra time, I managed to quickly find the UserForm code. I haven't used/created any forms yet! That will be very helpful.

Thank you both for answering my original question and providing a more professional alternative.