Solved

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

Posted on 2016-09-25
9
48 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)
Comment Utility
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
Comment Utility
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
Comment Utility
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)
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Omar Hernandez
Comment Utility
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)
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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 78

Expert Comment

by:David Johnson, CD, MVP
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

744 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