Solved

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

Posted on 2016-09-25

1 solution

Medium Priority

87 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

Comment

Question by:Omar Hernandez

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

9 Comments

LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)

ID: 418152472016-09-25

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

Author Comment

by:Omar Hernandez

ID: 418152552016-09-25

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.

LVL 47

Accepted Solution

by:Wayne Taylor (webtubbs)

Wayne Taylor (webtubbs) earned 2000 total points

ID: 418152782016-09-25

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"

LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)

ID: 418152792016-09-25

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.

Author Comment

by:Omar Hernandez

ID: 418152942016-09-25

Same Error, file is on attachment maybe is me but every time i add it no luck,
completed-9-25-open.xlsm

LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)

ID: 418153032016-09-25

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.

LVL 81

Expert Comment

by:byundt

ID: 418153102016-09-25

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

Author Closing Comment

by:Omar Hernandez

ID: 418153112016-09-25

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.

LVL 82

Expert Comment

by:David Johnson, CD, MVP

ID: 418153312016-09-25

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

Featured Post

Question has a verified solution.

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

Excel - Did You Know !

Article by: Shums

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…

Automating Microsoft Excel from Access (Part I)

Article by: Dale Fye

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…

A Comprehensive Guide to Excel Charts for Marketers

Video by: AnnieCushing

This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

Microsoft Excel

How to Create Calendar Reports from Access Queries

Video by: crystal

Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data! ... really see it To use the code to create a calendar from a q…