[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).

Copy to clipboard
Private Sub wsList_Click()

Dim nSheet As String

nSheet = wsList.Value

Set shtName = Sheets(nSheet)

shtName.Activate
     
End Sub

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

Copy to clipboard
Private Sub import()

Set mainWb = ActiveWorkbook

MsgBox shtName.Name

copyData 'this procedure is working correctly as I am using activesheet as reference from workbooks(2).

Set GetRng = Application.InputBox("Select the first header WITHIN data area!", _
"Row Collector", Type:=8)

strRow = GetRng.Row 'Getting row

Debug.Print strRow
Debug.Print mainWb.Name
Debug.Print shthame.Name ' I put a breakpoint here to see if this was the problem. MainWb was captured correct but variable ShtName was not used in this procedure.

'copying the relevant range

With mainWb.shtName
        i = .Cells(strRow + 1, 1).End(xlDown).Row 'this is row number
        Debug.Print "The value of i is " & i
        .Cells(i, 1).Paste
End With


End Sub

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:

Public shtName As Worksheet 'tried to change this to Public
Private mainWb As Workbook
Private iWb As Workbook
__________________________________________________________

Private Sub AppendBtn_Click()

Set mainWb = Workbooks(1)

'if cleardata.value is true then start cleaning + pasting
If ClearData.Value = True Then
cleanAndimport
Else
import
End If



End Sub
__________________________________________________________

Private Sub wsList_Click()

Dim nSheet As String

nSheet = wsList.Value

Set shtName = Sheets(nSheet)

shtName.Activate

End Sub
Private Sub UserForm_Initialize()

Dim n As Long

For n = 1 To ActiveWorkbook.Sheets.Count
wsList.AddItem ActiveWorkbook.Sheets(n).Name
Next n

End Sub
__________________________________________________________

Private Sub ClearData_Click() 'This is a simple checkbox
End Sub
__________________________________________________________
Private Sub CloseBtn_Click()
Unload Me
End Sub
__________________________________________________________
Private Sub copyData()

Dim strRow As Integer
Dim OneRowBelow As Long
Dim GetRng As Range

'Get file path for setting workbook object
f = Application.GetOpenFilename(filefilter:="Excel Files,*.xl*;*.xm*")

Set iWb = Workbooks.Open(f)

Set GetRng = Application.InputBox("Select the first header WITHIN data area!", _
"Row Collector", Type:=8)

strRow = GetRng.Row 'Getting row

Debug.Print strRow

'copying the relevant range

With iWb.ActiveSheet
i = .Cells(strRow + 1, 1).End(xlDown).Row 'this is row number
Debug.Print i
j = .Cells(strRow + 1, Columns.Count).End(xlToLeft).Column
Debug.Print j
.Range(Cells(strRow + 1, 1), Cells(i, j)).Copy
End With

iWb.Close

End Sub
__________________________________________________________

Private Sub import()

Set mainWb = ActiveWorkbook

MsgBox shtName.Name

copyData

Set GetRng = Application.InputBox("Select the first header WITHIN data area!", _
"Row Collector", Type:=8)

strRow = GetRng.Row 'Getting row

Debug.Print strRow
Debug.Print mainWb.Name
Debug.Print shthame.Name

'copying the relevant range

With mainWb.shtName
i = .Cells(strRow + 1, 1).End(xlDown).Row 'this is row number
Debug.Print "The value of i is " & i
.Cells(i, 1).Paste
End With


End Sub
__________________________________________________________

Private Sub cleanAndimport()

Dim strRow As Integer
Dim OneRowBelow As Long
Dim GetRng As Range

copyData

Set GetRng = Application.InputBox("Select the first header WITHIN data area!", _
"Row Collector", Type:=8)

With mainWb.shtName
'clearing sheet
i = .Cells(strRow + 1, 1).End(xlDown).Row 'this is row number
Debug.Print i
j = .Cells(strRow + 1, Columns.Count).End(xlToLeft).Column
Debug.Print j
.Range(Cells(strRow + 1, 1), Cells(i, j)).Clear
i = .Cells(strRow + 1, 1).End(xlDown).Row 'this is row number
.Range(Cells(strRow + 1, 1), Cells(i, 1)).Paste
End With

End Sub

There are parts of your code where you refer to:

Debug.Print shthame.Name

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:

Application.CutCopyMode = False

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

Hope this helps

Two words: Option Explicit.

With mainWb.shtName

should be:

With shtName

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.