[UserForm's Listbox]Why is sheetname empty when using it in another procedure?

Problem: using listbox.value (for sheet name selection) in another procedure results in nothing?

thwuserform.png

Trying to use shtName selection in the list box to use it in another procedure but this is not returning anything after checking debugger. Full code at the end shows that I declared shtName as public and in declarations section (whole code behind userform).

Please Login or Register  to view this content.

After drilling down, I find shtName variable to be empty.

Please Login or Register  to view this content.

Things that I have tried:

- Putting shtName var in declarations section: Public shtName as worksheet
- Setting shtName to worksheet object using Set keyword.

Is there any other listbox method I should be aware of?

One other question if you could answer:

• How to avoid standard message that states to clear clipboard (at the end of copyData procedure)? Programmatically.

For reference, here is the full code:

Please Login or Register  to view this content.

There are parts of your code where you refer to:

Please Login or Register  to view this content.

The above will not give a result as shthame has not been defined, could this typo be your issue?

To clear the clipboard use the below after the paste line:

Please Login or Register  to view this content.

The other option for a variable could be 'Global' have you tried to declare as 'Global'?

Hope this helps

Two words: Option Explicit.

Please Login or Register  to view this content.

should be:

Please Login or Register  to view this content.

Originally Posted by immigrated4urjob

I guess the question is that mainWb is implied and I don't need to reference it?

When you assign a sheet to the variable, it is a specific sheet which can only be in one workbook, so there is no need to qualify it further. If you use MainWb.shtname you should get a 438 error since you are effectively trying to access a shtname property of a workbook, and that doesn't exist.