Solved

Have two different functions work in conjunction when using one button.

Posted on 2016-09-25
9
56 Views
Last Modified: 2016-09-25
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

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

Open in new window


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

Open in new window

0
Comment
Question by:Omar Hernandez
9 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41815247
Just call the FindAndPlaceValuesInColumnC() routine from the CommandButton4_Click() event. If you want it to run after the Click event, move the call line to the end.

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

Open in new window

0
 

Author Comment

by:Omar Hernandez
ID: 41815255
Hello there Webtubbs, when i copy and paste the code to the button location it gives me an error Compile error: Ambiguous name detecte: FindAndPlaceValuesInColumnC.

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.
0
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 500 total points
ID: 41815278
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 FindAndPlaceValuesInColumnC routine can be in a regular module.

All you need to do is add this line...

Call FindAndPlaceValuesInColumnC

Open in new window


...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"
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41815279
BTW - the error you received was because after you copied the FindAndPlaceValuesInColumnC macro you had 2 macros in your project with the same name, which is not permitted.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Omar Hernandez
ID: 41815294
Same Error, file is on attachment maybe is me but every time i add it no luck,
completed-9-25-open.xlsm
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41815303
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 'FindAndPlaceValuesInColumnC' routines, one in Module 2, one in Class Module 1 and again in Module 17. As I've previously mentioned, there error was because you had more than 1 macro with the same name in your project.
0
 
LVL 81

Expert Comment

by:byundt
ID: 41815310
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:
'Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Open in new window


Finally, I added the Call Sub FindAndPlaceValuesInColumnC statement at the end of sub CommandButton4_Click in userform NumberArange as suggested by Wayne.

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
1
 

Author Closing Comment

by:Omar Hernandez
ID: 41815311
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.
0
 
LVL 79

Expert Comment

by:David Johnson, CD, MVP
ID: 41815331
All subs in 1 module
2 subs changed name
FindAndPlaceValuesInColumnC to FindAndPlaceValuesInColumnG  // as it references columnG not C
FindAndPlaceValuesInColumnC to FindAndPlaceValuesInColumnC1 // not sure about this one
Copy-of-completed-9-25-open.xlsm
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now