+ Reply to Thread
Results 1 to 4 of 4

ComboBox in USERFORM used to change which sheet output goes

Hybrid View

  1. 04-06-2016, 02:58 PM #1
    InvalidTxtString
    InvalidTxtString is offline
    Registered User
    Join Date
    04-06-2016
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    89

    ComboBox in USERFORM used to change which sheet output goes

    In the attached file, I'm using a userform to fill 6 cells and print the sheet.

    I have some hidden sheets in the workbook, which can be choosen (only by name) from the combobox in my userform.

    -How can I make it so that if I choose a different sheet in the combobox, the userform applies to that sheet?

    (PS: I know its the same cell in my module for each sheet - i'll be aligning the cell different on each sheet, so the sheets will have a different location for the print.)

    EDIT: I for some reason can't attach the sheet directly?

    http://www.excelforum.com/attachment...1&d=1459969643
    Attached Files Attached Files
    Last edited by InvalidTxtString; 04-06-2016 at 03:07 PM.
    Reply With QuoteRegister To Reply
  2. 04-06-2016, 03:58 PM #2
    Arkadi
    Arkadi is offline
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059

    Re: ComboBox in USERFORM used to change which sheet output goes

    maybe a select case statement would work well in this case? 
    Private Sub CommandButton1_Click()

Select Case ComboBox1.Text
    Case "MHT"
        Worksheets("MHT").Range("C15").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT").Range("H15").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT").Range("C35").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT").Range("H35").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT").Range("C55").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT").Range("H55").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
    Case "MHT2"
        Worksheets("MHT2").Range("C15").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT2").Range("H15").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT2").Range("C35").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT2").Range("H35").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT2").Range("C55").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT2").Range("H55").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
    Case "MHT3"
        Worksheets("MHT3").Range("C15").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT3").Range("H15").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT3").Range("C35").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT3").Range("H35").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT3").Range("C55").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT3").Range("H55").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
    Case "MHT4"
        Worksheets("MHT4").Range("C15").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT4").Range("H15").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT4").Range("C35").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT4").Range("H35").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT4").Range("C55").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT4").Range("H55").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
    Case "MHT5"
        Worksheets("MHT5").Range("C15").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT5").Range("H15").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT5").Range("C35").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT5").Range("H35").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT5").Range("C55").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
        Worksheets("MHT5").Range("H55").Value = "PROD " & TBProd.Value & " - " & "MHT " & TBmht.Value
End Select
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.
    Reply With QuoteRegister To Reply
  3. 04-06-2016, 04:08 PM #3
    InvalidTxtString
    InvalidTxtString is offline
    Registered User
    Join Date
    04-06-2016
    Location
    Sweden
    MS-Off Ver
    2016
    Posts
    89

    Re: ComboBox in USERFORM used to change which sheet output goes

    @Arkadi

    That works perfect!

    Only thing now is - how do I get it to print the right sheet also?

    I'm using:

    Application.Dialogs(xlDialogPrint).Show

UFmht.Hide
       
End Sub
    But that opens the dialog for printing the active sheet
    Reply With QuoteRegister To Reply
  4. 04-06-2016, 06:31 PM #4
    Arkadi
    Arkadi is offline
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,059
    Where you assign the values inside the case statement just add worksheet("name").activate just change name to the actual name of course. You may also need to unhide it to print. Also .hide hides the form but it is still in memory... You may want to change Ufhm.hide to unload me
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Userform: Change Combobox list based on a second ComboBox selection
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-11-2016, 03:45 PM
  2. Userform Combobox to populate form: Can I change the order of the columns in the combobox?
    By CraigWiggins in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-31-2014, 12:14 PM
  3. [SOLVED] cannot change userform combobox range error 457
    By cfinch100 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-08-2014, 05:22 PM
  4. Change Userform Label based on ComboBox Selection
    By rbyrd023 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-02-2014, 09:25 AM
  5. [SOLVED] Change Labels in Userform based on selection in ComboBox
    By johnw993 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-03-2013, 10:52 PM
  6. x2 Combobox's to filter sheet then output results from that row to a textbox
    By JonNot in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-07-2012, 04:47 PM
  7. [SOLVED] UserForm ComboBox Change Event Question
    By tja1964 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-10-2012, 07:21 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1