Help with combo boxes and macros

Bacchus815

New Member
Joined
Jul 30, 2007
Messages
5
I'm working on a project for work and would really appreciate some help.

I have a combo box with 87 combinations. When a user selects something, I need the macro to run in another worksheet and show only the data for that combination.

Currently, it will run in the same sheet as my combo box and, seemingly, for all the data.

I'm pretty new with macros, so would really appreciate if someone would let me know how to get back on track.

Thank you!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Bacchus815

New Member
Joined
Jul 30, 2007
Messages
5
Clearly I was having a "brainfart" on the first part of my problem as I FINALLY realized the reason my macro was starting in the same sheet as my combo box was that I wasn't telling the macro to go from that sheet to the sheet with the data.

However, I still can't figure out how to code the macro where, when it's ran, it will only pull the data for the combination selected. It's not even accepting the autofilter I'm putting into the column that's the same as what's in my combo boxes.

Below is my code. I'll be eternally grateful for any help. I really want to get this project done and, at the end of the day, I will have learned something too. I'll be happy to e-mail the file to anyone who wouldn't mind lending a hand.

Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 8/1/2007 by Alfred Sumrall
'
' Keyboard Shortcut: Ctrl+Shift+I
'
    Sheets("BY FAC & UNIT").Select
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],""  --  "",RC[-1])"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C2559")
    Range("C2:C2559").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Cells.Select
    Application.CutCopyMode = False
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Facility & Nursing Unit"
    Columns("A:C").Select
    Range("C1").Activate
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Columns("C:C").Select
    Selection.AutoFilter
    Columns("C:C").Select
    Selection.EntireColumn.Hidden = True
    Columns("D:D").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Selection.ColumnWidth = 45
    ActiveWindow.ScrollColumn = 1
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Columns("E:K").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .ShrinkToFit = False
        .MergeCells = False
    End With
    Range("F1:K1").Select
    Range("K1").Activate
    Selection.Replace What:="-", Replacement:="/1/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    Selection.NumberFormat = "mmm-yy"
    Range("A1:K1").Select
    Range("K1").Activate
    Selection.Font.Bold = True
    Selection.Interior.ColorIndex = 36
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = 1
    End With
    Columns("F:K").Select
    Range("K1").Activate
    Selection.ColumnWidth = 11
    Columns("E:E").Select
    Selection.ColumnWidth = 12
    ActiveWindow.ScrollColumn = 1
    Range("E2:K2559").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="0", Formula2:="0.8499"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 2
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="0.85", Formula2:="0.8999"
    With Selection.FormatConditions(2).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 1
    End With
    Selection.FormatConditions(2).Interior.ColorIndex = 6
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="0.9", Formula2:="1"
    With Selection.FormatConditions(3).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 2
    End With
    Selection.FormatConditions(3).Interior.ColorIndex = 10
    ActiveWindow.ScrollColumn = 1
    Range("E2").Select
    ActiveWindow.FreezePanes = True
    Range("A1:K2559").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("E2").Select
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""Arial,Bold""&12Methodist Le Bonheur Healthcare" & Chr(10) & "The Joint Commission Database&""Arial,Regular""&10" & Chr(10) & "&""Arial,Bold Italic""&12Level of Analysis:  By Facility && Nursing Unit"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "&D" & Chr(10) & "Page &P of &N"
        .LeftMargin = Application.InchesToPoints(0)
        .RightMargin = Application.InchesToPoints(0)
        .TopMargin = Application.InchesToPoints(1.25)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 5
    Cells.Select
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("A2").Select
End Sub
[/quote]
 

Forum statistics

Threads
1,181,730
Messages
5,931,716
Members
436,800
Latest member
abowalid98

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
Top