Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enable Command Button Procedure
I have 8 command buttons. As you can see by my code enables and disables them
depending on the click. How can I rewrite this code to just call a procedure to do this instead of the way I have it repeated 8 diffrent times??? Thanks Option Explicit Sub SortThisSheet() Dim LastRow As Long Application.EnableEvents = False With Me If .FilterMode Then .ShowAllData End If LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("a5:L" & LastRow) .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, Orientation:=xlTopToBottom End With End With Application.EnableEvents = True 'AutoFilterMode = False End Sub 'charge off date date()+10 Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count 1 Then Exit Sub If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 2).ClearContents Else With .Offset(0, 2) .Value = Date + 10 End With End If Application.EnableEvents = True End If End With 'pick data from code sheet for rescode If Target.Cells.Count 1 Then Exit Sub If Target.Column = 9 Then If Target.Value = "" Then Exit Sub Application.EnableEvents = False If (IsError(Application.VLookup(Target.Value, Worksheets("Codes").Range("CodeData"), 2, 0))) Then Target.Value = "" Else Target.Value = Application.VLookup(Target.Value, Worksheets("Codes").Range("CodeData"), 2, 0) End If Application.EnableEvents = True End If End Sub Private Sub ActiveRecords_Click() Application.ScreenUpdating = False Band3.Enabled = True Band4.Enabled = True Band5.Enabled = True Manager.Enabled = True Military.Enabled = True ActiveRecords.Enabled = False ReportPreview.Enabled = True Call SortThisSheet Range("A5").AutoFilter Field:=3 Range("A5").AutoFilter Field:=12, Criteria1:="=" Application.ScreenUpdating = True End Sub Private Sub Band3_Click() Application.ScreenUpdating = False Band3.Enabled = False Band4.Enabled = True Band5.Enabled = True Manager.Enabled = True Military.Enabled = True ActiveRecords.Enabled = True ReportPreview.Enabled = True Call SortThisSheet Range("A5").AutoFilter Field:=3, Criteria1:="Band 3" Range("A5").AutoFilter Field:=12, Criteria1:="=" Application.ScreenUpdating = True End Sub Private Sub Band4_Click() Application.ScreenUpdating = False Band3.Enabled = True Band4.Enabled = False Band5.Enabled = True Manager.Enabled = True Military.Enabled = True ActiveRecords.Enabled = True ReportPreview.Enabled = True Call SortThisSheet Range("A5").AutoFilter Field:=3, Criteria1:="Band 4" Range("A5").AutoFilter Field:=12, Criteria1:="=" Application.ScreenUpdating = True End Sub Private Sub Band5_Click() Application.ScreenUpdating = False Band3.Enabled = True Band4.Enabled = True Band5.Enabled = False Manager.Enabled = True Military.Enabled = True ActiveRecords.Enabled = True ReportPreview.Enabled = True Call SortThisSheet Range("A5").AutoFilter Field:=3, Criteria1:="Band 5" Range("A5").AutoFilter Field:=12, Criteria1:="=" Application.ScreenUpdating = True End Sub Private Sub Manager_Click() Application.ScreenUpdating = False Band3.Enabled = True Band4.Enabled = True Band5.Enabled = True Manager.Enabled = False Military.Enabled = True ActiveRecords.Enabled = True ReportPreview.Enabled = True Call SortThisSheet Range("A5").AutoFilter Field:=3, Criteria1:="Manager" Range("A5").AutoFilter Field:=12, Criteria1:="=" Application.ScreenUpdating = True End Sub Private Sub Military_Click() Application.ScreenUpdating = False Band3.Enabled = True Band4.Enabled = True Band5.Enabled = True Manager.Enabled = True Military.Enabled = False ActiveRecords.Enabled = True ReportPreview.Enabled = True Call SortThisSheet Range("A5").AutoFilter Field:=3, Criteria1:="Military" Range("A5").AutoFilter Field:=12, Criteria1:="=" Application.ScreenUpdating = True End Sub Private Sub ReportPreview_Click() Application.ScreenUpdating = False Band3.Enabled = True Band4.Enabled = True Band5.Enabled = True Manager.Enabled = True Military.Enabled = True ActiveRecords.Enabled = True ReportPreview.Enabled = False Call SortThisSheet 'Range("A5:L5").AutoFilter Range("A5").AutoFilter Field:=3 Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd, Criteria2:="<" End Sub Private Sub DailyReport_Click() Worksheets("Tracker").Range("A5").AutoFilter Field:=3 ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd, Criteria2:="<" Application.ScreenUpdating = False Workbooks.Open Filename:= _ "C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls" Windows("New Tracker.xls").Activate Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy Windows("Daily Tracker Report.xls").Activate Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues Windows("New Tracker.xls").Activate Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy Windows("Daily Tracker Report.xls").Activate Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues Windows("New Tracker.xls").Activate Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy Windows("Daily Tracker Report.xls").Activate Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues Worksheets("sheet1").Range("A5").Select Windows("New Tracker.xls").Activate 'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 'Selection.EntireRow.Delete 'Windows("Daily Tracker Report.xls").Activate Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent" Application.ScreenUpdating = True Call ActiveRecords_Click End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Enable Command Button Procedure
Hi Kenny,
You can call routines and pass parameters to them like this example:- Dim callValue1 As Variant Dim callValue2 As Variant Private Sub CommandButton1_Click() callValue1 = "This is from button 1" callValue2 = "Another from button 1" Call Repeat_Routine(callValue1, callValue2) End Sub Private Sub CommandButton2_Click() callValue1 = "This is from button 2" callValue2 = "Another from button 2" Call Repeat_Routine(callValue1, callValue2) End Sub Private Sub CommandButton3_Click() callValue1 = "This is from button 3" callValue2 = "Another from button 3" Call Repeat_Routine(callValue1, callValue2) End Sub Sub Repeat_Routine(myValue1, myValue2) MsgBox myValue1 & " " & myValue2 End Sub Regards, OssieMac "Kenny" wrote: I have 8 command buttons. As you can see by my code enables and disables them depending on the click. How can I rewrite this code to just call a procedure to do this instead of the way I have it repeated 8 diffrent times??? Thanks Option Explicit Sub SortThisSheet() Dim LastRow As Long Application.EnableEvents = False With Me If .FilterMode Then .ShowAllData End If LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("a5:L" & LastRow) .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _ Header:=xlYes, Orientation:=xlTopToBottom End With End With Application.EnableEvents = True 'AutoFilterMode = False End Sub 'charge off date date()+10 Private Sub Worksheet_Change(ByVal Target As Range) With Target If .Count 1 Then Exit Sub If Intersect(Range("D:D"), .Cells) = "Out For Repo" Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 2).ClearContents Else With .Offset(0, 2) .Value = Date + 10 End With End If Application.EnableEvents = True End If End With 'pick data from code sheet for rescode If Target.Cells.Count 1 Then Exit Sub If Target.Column = 9 Then If Target.Value = "" Then Exit Sub Application.EnableEvents = False If (IsError(Application.VLookup(Target.Value, Worksheets("Codes").Range("CodeData"), 2, 0))) Then Target.Value = "" Else Target.Value = Application.VLookup(Target.Value, Worksheets("Codes").Range("CodeData"), 2, 0) End If Application.EnableEvents = True End If End Sub Private Sub ActiveRecords_Click() Application.ScreenUpdating = False Band3.Enabled = True Band4.Enabled = True Band5.Enabled = True Manager.Enabled = True Military.Enabled = True ActiveRecords.Enabled = False ReportPreview.Enabled = True Call SortThisSheet Range("A5").AutoFilter Field:=3 Range("A5").AutoFilter Field:=12, Criteria1:="=" Application.ScreenUpdating = True End Sub Private Sub Band3_Click() Application.ScreenUpdating = False Band3.Enabled = False Band4.Enabled = True Band5.Enabled = True Manager.Enabled = True Military.Enabled = True ActiveRecords.Enabled = True ReportPreview.Enabled = True Call SortThisSheet Range("A5").AutoFilter Field:=3, Criteria1:="Band 3" Range("A5").AutoFilter Field:=12, Criteria1:="=" Application.ScreenUpdating = True End Sub Private Sub Band4_Click() Application.ScreenUpdating = False Band3.Enabled = True Band4.Enabled = False Band5.Enabled = True Manager.Enabled = True Military.Enabled = True ActiveRecords.Enabled = True ReportPreview.Enabled = True Call SortThisSheet Range("A5").AutoFilter Field:=3, Criteria1:="Band 4" Range("A5").AutoFilter Field:=12, Criteria1:="=" Application.ScreenUpdating = True End Sub Private Sub Band5_Click() Application.ScreenUpdating = False Band3.Enabled = True Band4.Enabled = True Band5.Enabled = False Manager.Enabled = True Military.Enabled = True ActiveRecords.Enabled = True ReportPreview.Enabled = True Call SortThisSheet Range("A5").AutoFilter Field:=3, Criteria1:="Band 5" Range("A5").AutoFilter Field:=12, Criteria1:="=" Application.ScreenUpdating = True End Sub Private Sub Manager_Click() Application.ScreenUpdating = False Band3.Enabled = True Band4.Enabled = True Band5.Enabled = True Manager.Enabled = False Military.Enabled = True ActiveRecords.Enabled = True ReportPreview.Enabled = True Call SortThisSheet Range("A5").AutoFilter Field:=3, Criteria1:="Manager" Range("A5").AutoFilter Field:=12, Criteria1:="=" Application.ScreenUpdating = True End Sub Private Sub Military_Click() Application.ScreenUpdating = False Band3.Enabled = True Band4.Enabled = True Band5.Enabled = True Manager.Enabled = True Military.Enabled = False ActiveRecords.Enabled = True ReportPreview.Enabled = True Call SortThisSheet Range("A5").AutoFilter Field:=3, Criteria1:="Military" Range("A5").AutoFilter Field:=12, Criteria1:="=" Application.ScreenUpdating = True End Sub Private Sub ReportPreview_Click() Application.ScreenUpdating = False Band3.Enabled = True Band4.Enabled = True Band5.Enabled = True Manager.Enabled = True Military.Enabled = True ActiveRecords.Enabled = True ReportPreview.Enabled = False Call SortThisSheet 'Range("A5:L5").AutoFilter Range("A5").AutoFilter Field:=3 Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd, Criteria2:="<" End Sub Private Sub DailyReport_Click() Worksheets("Tracker").Range("A5").AutoFilter Field:=3 ActiveSheet.Range("A5").AutoFilter Field:=12, Criteria1:="<Sent", Operator:=xlAnd, Criteria2:="<" Application.ScreenUpdating = False Workbooks.Open Filename:= _ "C:\Users\Kenny\Documents\Toyota\Floater\Daily Tracker Report.xls" Windows("New Tracker.xls").Activate Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)).Copy Windows("Daily Tracker Report.xls").Activate Worksheets("sheet1").Range("A5").PasteSpecial Paste:=xlPasteValues Windows("New Tracker.xls").Activate Range(("A6:B6"), Cells(Rows.Count, ("B:B")).End(xlUp)).Copy Windows("Daily Tracker Report.xls").Activate Worksheets("sheet1").Range("B5").PasteSpecial Paste:=xlPasteValues Windows("New Tracker.xls").Activate Range(("G6:K6"), Cells(Rows.Count, ("G:G")).End(xlUp)).Copy Windows("Daily Tracker Report.xls").Activate Worksheets("sheet1").Range("D5").PasteSpecial Paste:=xlPasteValues Worksheets("sheet1").Range("A5").Select Windows("New Tracker.xls").Activate 'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 'Selection.EntireRow.Delete 'Windows("Daily Tracker Report.xls").Activate Range(("L6"), Cells(Rows.Count, ("L:L")).End(xlUp)) = "Sent" Application.ScreenUpdating = True Call ActiveRecords_Click End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enable Command Button base on UserName | Excel Discussion (Misc queries) | |||
Enable Command Button base on UserName | Excel Worksheet Functions | |||
Enable Command Button base on UserName | Links and Linking in Excel | |||
Enable the Options button from the Tools Menu | Excel Worksheet Functions | |||
Enable Command Button After Entering Text | New Users to Excel |