Download The Workbook

Private Sub ComboBox1_Change()
Set sht = ActiveWorkbook.Sheets(Me.ComboBox1.Text)
sht.Activate
Dim Colcount As Integer
Colcount = sht.Range("A1").CurrentRegion.Columns.Count

Dim RngAddress As String
LastRow = sht.Range("A1").End(xlDown).Row

RngAddress = sht.Range(Cells(1, 1), Cells(LastRow, Colcount)).Address
FormatForm = Format_Userform(Colcount, RngAddress)

End Sub

Private Sub CommandButton1_Click()
r = 1
Dim OutputSh As Worksheet
Set OutputSh = ActiveWorkbook.Sheets("Output")

OutputSh.Activate
OutputSh.UsedRange.ClearContents
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
For C = 0 To Me.ListBox1.ColumnCount - 1
OutputSh.Cells(r, C + 1).Value = Me.ListBox1.List(i, C)
Next
r = r + 1
End If
Next
Unload UserForm1
End Sub

Private Sub UserForm_Initialize()
Me.Width = 350
Me.Height = 110
Me.ListBox1.Visible = False
Me.CommandButton1.Visible = False
Me.CommandButton2.Top = 45
Me.CommandButton2.Left = 150
With Me.CommandButton2
.BackColor = RGB(150, 0, 0)
.ForeColor = RGB(255, 255, 255)
.Font.Size = 18
Font.Name = "Estrangelo Edessa"

End With

With Me.ComboBox1
.Font.Size = 15
.Font.Name = "Estrangelo Edessa"
.Top = 5
.Height = 25
.Left = 150
.Width = 150
.ForeColor = RGB(255, 255, 255)
.Font.Bold = True
.TextAlign = fmTextAlignCenter
.BackColor = RGB(150, 0, 0)
End With
Dim S As Worksheet
For Each S In ActiveWorkbook.Worksheets
If S.Name <> "Buttons" And S.Name <> "Output" And S.Name <> "Sheet1" Then
Me.ComboBox1.AddItem S.Name
End If
Next

With Me.Label1
.Font.Size = 15
.Font.Name = "Estrangelo Edessa"
.Top = 5
.Height = 20
.Caption = "Sheet Name"
.Font.Bold = True
.Left = 5
.Width = 110
.ForeColor = RGB(255, 255, 255)
.TextAlign = fmTextAlignCenter
.BackColor = RGB(150, 0, 0)
End With
End Sub

Function Format_Userform(Colcount, RngAddress)

UserForm1.ListBox1.Visible = True
UserForm1.CommandButton1.Visible = True

Dim UserformWidth As Long
UserformWidth = Colcount * 100

If UserformWidth < 400 Then
UserformWidth = 400
End If

If UserformWidth > 600 Then
UserformWidth = 600
End If

'Defined the height of the Userform based on Number of rows in a worksheet
Dim UserformHeight As Long
UserformHeight = sht.Range("A1").CurrentRegion.Rows.Count * 18

'Add additional 200 for command buttons
UserformHeight = UserformHeight + 200

If UserformHeight > 450 Then
UserformHeight = 450
End If

If UserformHeight < 400 Then
UserformHeight = 400
End If

'===========Format the Userform================
With UserForm1
.Width = UserformWidth

.Height = UserformHeight
.BackColor = RGB(210, 95, 95)
.BorderColor = RGB(255, 0, 0)
.BorderStyle = fmBorderStyleSingle
.Caption = "Select The Data"
End With
'==========Format the Listbox======================

With UserForm1.ListBox1

.Left = 5
.Top = 40

'From the Userform Height deducted 200 to place the command buttons
ListBoxHeight = UserformHeight - 200
.Height = ListBoxHeight

'Assigned 110 for each column of Listbox
ListboxWidth = 100 * Colcount


.Width = UserformWidth - 20


.ColumnCount = Colcount
.ColumnHeads = False

'Provided the column width as 100
For C = 1 To Colcount
ColWidth = ColWidth & ";" & 95
Next




ColWidth = Right(ColWidth, Len(ColWidth) - 1)
.ColumnWidths = ColWidth
.RowSource = "=" & sht.Name & "!" & RngAddress
Application.Wait (Now + TimeValue("00:00:01"))
If Colcount >= 4 Then
.Width = UserformWidth - 20
End If

If Colcount = 1 Then
.Width = 150
End If

If Colcount = 2 Then
.Width = 250
End If

.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti
.Font.Size = 18
.ForeColor = RGB(0, 0, 0)
.Font.Name = "Estrangelo Edessa"
.BackColor = RGB(250, 240, 205)
.TextAlign = fmTextAlignLeft
End With

'========Formatting CommandButton1=====
With UserForm1.CommandButton1
.BackColor = RGB(140, 0, 0)
.ForeColor = RGB(255, 255, 255)
.Font.Size = 15
.Font.Name = "Estrangelo Edessa"
.Visible = True
'From the userform height deducted Listbox Top + Listbox Height + Commandbutton Height
RemainingSpace = UserformHeight - 40 - ListBoxHeight - 30

'Centered the remaing space to place the command buttons
HalfReaminingSpace = RemainingSpace / 2
.Top = 15 + ListBoxHeight + HalfReaminingSpace
.Left = 25
End With

'=======Formatting CommandButton2==============
With UserForm1.CommandButton2
.BackColor = RGB(140, 0, 0)
.ForeColor = RGB(255, 255, 255)
.Font.Size = 15
.Font.Name = "Estrangelo Edessa"
.Visible = True
.Top = 15 + ListBoxHeight + HalfReaminingSpace
.Left = UserformWidth - 150

End With

ColWidth = ""
Colcount = 0
RngAddress = ""
UserformWidth = 0
UserformHeight = 0
ListBoxHeight = 0
ListboxWidth = 0

Set sht = Nothing
ActiveWorkbook.Sheets("Buttons").Activate
End Function