Have two different functions work in conjunction when using one button.
I would like to make two function work in conjunction when i click on one button. Thanks again for your time and help.
I would like to have this code from Modules
To work in conjunction with this button that has another function.
I would like to have this code from Modules
Sub FindAndPlaceValuesInColumnC()
Dim lr As Long
Dim rng As Range, cell As Range
Application.ScreenUpdating = False
Application.EnableEvents = False
lr = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ActiveSheet.ListObjects("Table134").Range.AutoFilter field:=7, Criteria1:="1"
If Range("G4:G" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
Set rng = Range("F5:F" & lr).SpecialCells(xlCellTypeVisible)
For Each cell In rng
If InStr(Cells(cell.Row, "C").Value, " (") = 0 Then
Cells(cell.Row, "C") = Cells(cell.Row, "C") & " (" & Trim(Replace(cell.Value, "*", "")) & ")"
Else
Cells(cell.Row, "C") = WorksheetFunction.Replace(Cells(cell.Row, "C").Value, InStr(Cells(cell.Row, "C"), " ("), 255, " (" & Trim(Replace(cell.Value, "*", "")) & ")")
End If
Next cell
End If
ActiveSheet.ListObjects("Table134").Range.AutoFilter field:=7
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
To work in conjunction with this button that has another function.
Private Sub CommandButton4_Click()
'
' refresh Macro
'
'
ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
Add Key:=Range("Table134[[#All],[TIMER]]"), SortOn:=xlSortOnValues, Order _
:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
Add Key:=Range("Table134[[#All],[TASK]]"), SortOn:=xlSortOnValues, Order _
:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
Add Key:=Range("Table134[[#All],[ORGANIZER]]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Unload Me
End Sub
Just call the FindAndPlaceValuesInColumn
Private Sub CommandButton4_Click()
'
' refresh Macro
'
' call the other macro
Call FindAndPlaceValuesInColumnC
'
ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
Add Key:=Range("Table134[[#All],[TIMER]]"), SortOn:=xlSortOnValues, Order _
:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
Add Key:=Range("Table134[[#All],[TASK]]"), SortOn:=xlSortOnValues, Order _
:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort.SortFields. _
Add Key:=Range("Table134[[#All],[ORGANIZER]]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("GENERAL").ListObjects("Table134").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Unload Me
End Sub
Hello there Webtubbs, when i copy and paste the code to the button location it gives me an error Compile error: Ambiguous name detecte: FindAndPlaceValuesInColumn
I do want it to run after the Click event, but i do not understand the part where i would need to move the call line to the end.
May you please explain more in detail or have it so that i may copy and paste the code thanks again for your fast response.
I do want it to run after the Click event, but i do not understand the part where i would need to move the call line to the end.
May you please explain more in detail or have it so that i may copy and paste the code thanks again for your fast response.
Don't worry about moving the routines - they are fine where they are. The Click event will be in the userforms code module and the FindAndPlaceValuesInColumn
All you need to do is add this line...
...to the Click event which runs that macro. If you want to run it after the Click events code, add that line after "Unload Me"
All you need to do is add this line...
Call FindAndPlaceValuesInColumnC
...to the Click event which runs that macro. If you want to run it after the Click events code, add that line after "Unload Me"
BTW - the error you received was because after you copied the FindAndPlaceValuesInColumn
Same Error, file is on attachment maybe is me but every time i add it no luck,
completed-9-25-open.xlsm
completed-9-25-open.xlsm
You've got a bit of a mess there. Steps to clear it up...
1) Remove both class modules
2) Remove the empty modules 7, 8, 13, 14 and 16.
3) Remove module 17
4) Consolidate your macros into 1 or 2 sensibly renamed modules. Delete any empty modules.
You still received the error because you had 2 and a half 'FindAndPlaceValuesInColum
1) Remove both class modules
2) Remove the empty modules 7, 8, 13, 14 and 16.
3) Remove module 17
4) Consolidate your macros into 1 or 2 sensibly renamed modules. Delete any empty modules.
You still received the error because you had 2 and a half 'FindAndPlaceValuesInColum
I have two suggestions for you.
First, I highly recommend Option Explict in every module sheet. The VBA Editor will insert this for you automatically (in the future) if you check the box for "Require Variable Declaration" in the Tools...Options...Editor menu item. With Option Explicit, you must put every variable in a Dim statement. If your code contains a variable that hasn't been declared in a Dim statement, it will flag that fact as an error. Most commonly, that means I have a typo in my variable name.
Second, I recommend that you regularly use the Debug...Compile VBAProject menu item to make sure that your code doesn't contain a syntax problem or other error that is obvious to the VBA compiler. It is best to fix those errors before you try to test your code.
In the workbook you attached, Module17 contains two run-on subs. Debug...Compile VBAProject caught the error immediately. I fixed it by commenting out (putting a single quote before the statement begins) the first line in that module:
Finally, I added the Call Sub FindAndPlaceValuesInColumn
When trying to run your code, I got the same error message you did. I found copies of that sub in 3 places: class module Class1 and regular modules Module2 and Module17. I deleted it from Class1 and commented it out in Module17. The version in Module2 better resembled the code you posted.
Brad
completed-9-25-openQ28972276.xlsm
First, I highly recommend Option Explict in every module sheet. The VBA Editor will insert this for you automatically (in the future) if you check the box for "Require Variable Declaration" in the Tools...Options...Editor menu item. With Option Explicit, you must put every variable in a Dim statement. If your code contains a variable that hasn't been declared in a Dim statement, it will flag that fact as an error. Most commonly, that means I have a typo in my variable name.
Second, I recommend that you regularly use the Debug...Compile VBAProject menu item to make sure that your code doesn't contain a syntax problem or other error that is obvious to the VBA compiler. It is best to fix those errors before you try to test your code.
In the workbook you attached, Module17 contains two run-on subs. Debug...Compile VBAProject caught the error immediately. I fixed it by commenting out (putting a single quote before the statement begins) the first line in that module:
'Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Finally, I added the Call Sub FindAndPlaceValuesInColumn
When trying to run your code, I got the same error message you did. I found copies of that sub in 3 places: class module Class1 and regular modules Module2 and Module17. I deleted it from Class1 and commented it out in Module17. The version in Module2 better resembled the code you posted.
Brad
completed-9-25-openQ28972276.xlsm
Thanks a mil Wayne, definitely had a mess there. After deleting those that you have suggested gave me the result that i was looking for. Again thanks a mil Wayne.
All subs in 1 module
2 subs changed name
FindAndPlaceValuesInColumn
FindAndPlaceValuesInColumn
Copy-of-completed-9-25-open.xlsm
2 subs changed name
FindAndPlaceValuesInColumn
FindAndPlaceValuesInColumn
Copy-of-completed-9-25-open.xlsm
Premium Content
You need an Expert Office subscription to comment.Start Free Trial