activate a sheet in another workbook based on choices made in userform

R

RCBricker

Well-known Member
Joined
Feb 4, 2003
Messages
1,560
Hi guys!

I have an User Form I am using to generate different reports. this is done with a combo box. The user will choose the type of report, they will then select/open the data source upon making that choice. The user is then prompted to use another combo box to select which worksheet houses their data.

All of this works fine.

the problem

Upon making the choice I would like to copy the chosen worksheet in the now selected workbook, copy the selected worksheet and paste it into a new sheet in the original workbook.

I am currently getting a run time error 424 when I try and set my SHs (worksheet variable) to the value of the second combo box (which brings back the worksheet the data is housed in).

Below is my code:

Code: 
Option Explicit

    '=====================================================================
    '   Global Variables
    '=====================================================================
    
    Dim fname As String
    Dim Filepath As String
    Dim WBs As Workbook
    Dim WBd As Workbook
    Dim SHs As Worksheet
    Dim SHd As Worksheet
    Dim Sh As Worksheet
    Dim tabname As String
    


Private Sub CoBReport_Change()

    '=====================================================================
    '   establish values for the 4 data source text boxes
    '   based on the choice made in the report combo box
    '=====================================================================

    If report_source.CoBReport.Value = "DDRS B Level 2" Then
        report_source.TBDs1.Enabled = False
                report_source.TBDs1.Value = "DDRS B Level 2"
        report_source.TBDs2.Enabled = False
        report_source.TBDs3.Enabled = False
        report_source.TBDs4.Enabled = False
    Else
    If report_source.CoBReport.Value = "Budget Model Col A" Then
        report_source.TBDs1.Enabled = False
                report_source.TBDs1.Value = "SF 133"
        report_source.TBDs2.Enabled = False
                report_source.TBDs2.Value = "CMR"
        report_source.TBDs3.Enabled = False
        report_source.TBDs4.Enabled = False
    Else
    If report_source.CoBReport.Value = "Budget Model Col B" Then
        report_source.TBDs1.Enabled = False
                report_source.TBDs1.Value = "SF 133"
        report_source.TBDs2.Enabled = False
                report_source.TBDs2.Value = "CMR"
        report_source.TBDs3.Enabled = False
        report_source.TBDs4.Enabled = False
    Else
    If report_source.CoBReport.Value = "Budget Model Col E" Then
        report_source.TBDs1.Enabled = False
                report_source.TBDs1.Value = "SF 133"
        report_source.TBDs2.Enabled = False
                report_source.TBDs2.Value = "CMR"
        report_source.TBDs3.Enabled = False
        report_source.TBDs4.Enabled = False
    Else
    If report_source.CoBReport.Value = "Budget Model Col F" Then
        report_source.TBDs1.Enabled = False
                report_source.TBDs1.Value = "SF 133"
        report_source.TBDs2.Enabled = False
                report_source.TBDs2.Value = "CMR"
        report_source.TBDs3.Enabled = False
        report_source.TBDs4.Enabled = False
    Else
    If report_source.CoBReport.Value = "Budget Model Col H" Then
        report_source.TBDs1.Enabled = False
                report_source.TBDs1.Value = "SF 133"
        report_source.TBDs2.Enabled = False
                report_source.TBDs2.Value = "CMR"
        report_source.TBDs3.Enabled = False
        report_source.TBDs4.Enabled = False
    Else
    If report_source.CoBReport.Value = "Budget Compilation" Then
        report_source.TBDs1.Enabled = False
                report_source.TBDs1.Value = "SF 133"
        report_source.TBDs2.Enabled = False
        report_source.TBDs3.Enabled = False
        report_source.TBDs4.Enabled = False
    Else
    If report_source.CoBReport.Value = "SBR" Then
        report_source.TBDs1.Enabled = False
                report_source.TBDs1.Value = "SF 133"
        report_source.TBDs2.Enabled = False
        report_source.TBDs3.Enabled = False
        report_source.TBDs4.Enabled = False
    Else
    If report_source.CoBReport.Value = "Treasury Roll Forward" Then
        report_source.TBDs1.Enabled = False
                report_source.TBDs1.Value = "CMR"
        report_source.TBDs2.Enabled = False
        report_source.TBDs3.Enabled = False
        report_source.TBDs4.Enabled = False
    Else
    If report_source.CoBReport.Value = "Treasury Crosswalk" Then
        report_source.TBDs1.Enabled = False
                report_source.TBDs1.Value = "Pile File"
        report_source.TBDs2.Enabled = False
        report_source.TBDs3.Enabled = False
        report_source.TBDs4.Enabled = False
    Else
    If report_source.CoBReport.Value = "1.4.1 Analysis" Then
        report_source.TBDs1.Enabled = False
                report_source.TBDs1.Value = "DDRS B Level 2"
        report_source.TBDs2.Enabled = False
                report_source.TBDs2.Value = "SQL Transational Data"
        report_source.TBDs3.Enabled = False
        report_source.TBDs4.Enabled = False
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If

End Sub
Private Sub LBTB1FileName_Click()
    '=====================================================================
    '   Nothing as yet
    '=====================================================================

End Sub
Private Sub TBDs1_Change()

    '=====================================================================
    '   change label on form to reflect the data source name
    '=====================================================================

    LBTB1FileName.Caption = TBDs1
    
    '=====================================================================
    '   Message box to direct the user to select their data source
    '=====================================================================
    
    MsgBox "Please choose the " & TBDs1 & " file to use as a data source"
    
    '=====================================================================
    '   Calls in the sub for opening data workbook
    '=====================================================================
        
    getfileDS1

End Sub
Private Sub TBDs1filename_Change()

    '=====================================================================
    '   Nothing as yet
    '=====================================================================

End Sub
Private Sub UserForm_Initialize()

    '=====================================================================
    '   Populates report combo box with statice list
    '=====================================================================

        With CoBReport
            .AddItem "DDRS B Level 2"
            .AddItem "Budget Model Col A"
            .AddItem "Budget Model Col B"
            .AddItem "Budget Model Col E"
            .AddItem "Budget Model Col F"
            .AddItem "Budget Model Col H"
            .AddItem "Budget Compilation"
            .AddItem "SBR"
            .AddItem "Treasury Roll Forward"
            .AddItem "Treasury Crosswalk"
            .AddItem "1.4.1 Analysis"
        End With
        
    '=====================================================================
    '   Disables text boxes
    '=====================================================================
    
        report_source.TBDs1.Enabled = False
        report_source.TBDs2.Enabled = False
        report_source.TBDs3.Enabled = False
        report_source.TBDs4.Enabled = False
  
End Sub
Private Sub CoBuExit_Click()
    '=====================================================================
    '   defines action for Exit button
    '=====================================================================
    Unload Me
End Sub
Sub getfileDS1()

    Dim Filepath As String
    Dim WBs As Workbook
    Dim WBd As Workbook
    Dim SHs As Worksheet
    Dim SHd As Worksheet
    Dim Sh As Worksheet
    
    '=====================================================================
    '   sets Variable WBs as current workbook
    '=====================================================================

    Set WBs = ThisWorkbook
    
    '=====================================================================
    '   sets Variable filepath as current workbook's path
    '=====================================================================
    
    Filepath = ThisWorkbook.Path

    '=====================================================================
    '   Opens dialog box to browse and open data source file
    '=====================================================================

        With Application.FileDialog(msoFileDialogOpen)
            .Filters.Clear
            .InitialFileName = Filepath
            .AllowMultiSelect = False
        
                If .Show = True Then
                
                    fname = .SelectedItems(1)
                
                
                Else
                
                    MsgBox "Operation Cancelled"
                    Exit Sub
                
                End If
        
        End With

    '=====================================================================
    '   Sets text box name to fname variable (data soure path)
    '=====================================================================
        
        TBDs1filename.Value = fname

    '=====================================================================
    '   Populates CoBTabName1 with list of worksheets in data source WB
    '=====================================================================

        With CoBTabName1

            For Each Sh In Worksheets
                .AddItem Sh.Name
            Next Sh

        End With

    '=====================================================================
    '   prompts user to select a worksheet as data source worksheet
    '=====================================================================
        
        MsgBox "Please choose which tab for a data source"

    '=====================================================================
    '   focuses the user form on combo box so user can select worksheet
    '=====================================================================
        
        CoBTabName1.SetFocus
        'tabname = CoBTabName1.Value

End Sub

Private Sub CoBTabName1_Change()

'Set WBs = ThisWorkbook
'Set WBd = fname
'Set SHs = CoBTabName1
'WB2.SHs.Copy WB1.Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Data " & TBDs1 & ""


'*********  Run Time Error 424 - Object required   ***********
    Set SHs = report_source.CoBTabName1.Value
'*********  Run Time Error 424 - Object required   ***********


'==========  Don't know if this works as the above error stops the code   ===========
        SHs.Copy WBs.Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = _
        "Data " & TBDs1 & ""
'==========  Don't know if this works as the above error stops the code   ===========


    

'set shd=wb1.sheets(sheets.Add(after

End Sub

I will of course continue to research and try and solve this issue, but it is getting frustrating

Thanks in advance

Rich
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
338
tixarah
T
KlausW
  • Solved
import a sheet from a closed workbook from location in cell
Replies
3
Views
367
bobsan42
bobsan42
N
  • Solved
how to automatically clear text if text in another cell is removed
Replies
1
Views
141
Joe4
Joe4
H
  • Question Question
Copy Data where Source Workbook contains Name that is in destination sheet
Replies
4
Views
151
howard
H
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
108
RoryA
RoryA

Forum statistics

Threads
1,224,568
Messages
6,179,595
Members
452,927
Latest member
whitfieldcraig

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top