Merge components of multi-file macro into a single macro

I haven't heard from you since the weekend, but I have only just got back my internet at home due to a fault. What macros do you want to combine? The scrubber one is your original one that cleans data so I can't see why you want to combine them.

Have you checked the attached file?  It doesn't have a scrubber in it.  Please don't re-use old code.  I can't tell what's changed.

Well you've removed the code but left the module, but if you want to be rude then good luck

Roy, I wasn't trying to be rude.

I tend to miss it when someone responds with a change to code older than what I submitted.  It's hard to answer your question without referring to what I attached.  I have checked it and nothing has been removed.

I had scrubber code in a previous file, but that has nothing to do at all with the question posed here.

Thank you again for your help and I apologize in how I came across.  I really do appreciate your assistance very much.

Sorry, I misunderstood. As I actually said you left the scrubber module in but had removed the code. The only code in the workbook is actually the code that I wrote to show the list with the count. Scrubber and Delte empty columns are empty.

Let me know what you want to do and I'll take a look in the morning. I think UK is 5 or 6 hours ahead of you.

I'm trying to get the Listbox code together into a single module.

It is  - AddControls.

The Functions are used to calculate the required width and convert to pixesl. The code in the worksheet triggers the macro.

If you change this then it will not work how you specified previously

I need it to list the number of items next to each item.  If it does that we are good.

Right now, from what I can tell, there are three pieces to it.

The module
The function
The part that has to be copied to each sheet individually.

I am looking for one module only.

I have explained how to use this on multiple sheets or even workbooks previously.

For it to work on multiple sheets in the same workbook there are two ways depending on whether you want it tow ork on all sheets or just certain sheets.

Working on all sheets

If you want the code to run automatically on every sheet in the workbook then you can use the WorkBook events like this. AddControls is the name of the macro and simply type the name into the event and it will run automaticallt whenever the specifications for the macro to run re met.

Option Explicit

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
AddControls
End Sub

Select allOpen in new window

In this case the code should be added to the workbook code module:

1. Select the workbook in which you want to store the code
2. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
3. In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
4. Right-click on the ThisWorkbook object, and choose View Code
5. In the top right of the Window make sure the left hand drop down has Workbook selected. In the right hand one scroll down and find Workbook_SheetSelectionChange.
6. This is where you place the above code. Simply type AddControls  into the event

Add Excel VBA Code to a Worksheet Module

Select allOpen in new window

Some code is Event code, and will run automatically when a specific action occurs in  the worksheet For example, if you type in a cell, and press the Enter key, the worksheet has been changed. This would trigger the Worksheet_Change event. Worksheet event code is stored on a worksheet module. To make your code run in specific sheets as it does now simply follow these steps

1. Select the worksheet in which you want the code to run
2. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.Where the cursor is flashing type AddControls

Add Excel VBA Code to a Worksheet Module

Some code is Event code, and will run automatically when a specific action occurs in  the worksheet For example, if you type in a cell, and press the Enter key, the worksheet has been changed. This would trigger the Worksheet_Change event. Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:

1. Copy the code that you want to use
2. Select the worksheet in which you want the code to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.Where the cursor is flashing and type AddControls

I have actually demonstrated to you how to use this code in any workbook and provided links to in depth articles explaining this.

That works to get it from individual sheets, to the workbook.

So now I have code for it in two places (the module, and the workbook).  Is there anyway to merge the workbook code into the module, so that there is just one file to distribute for this purpose?

Thank you very much for working on this.

Here is how I experienced it https://www.screencast.com/t/hmyeDjwKDUX3

I can't see how to call it but am still experimenting.

Why would you want to call the macro? As it is now it runs when the cell is selected, if you want the code in am ordinary module then you would need to select the cell, then click a button to run the macro. That makes no sense.

I think you may be right.  I completely see your point and this is exactly what I was after!

Your last version appears to have one single block of code that makes clicking the header row for any cell produce a list box with totals.

Now that I have that in a single block of code, it's much easier to store and use in different sheets.

Would it be possible to use it all from within a module as opposed to a workbook?

In this way, the .bas file could be exported and re-imported as needed.

That's why I previously suggested an addin. If you have the code in one module you will lose the automatic running, unless you call it from the event code, which is actually what we had originally.

I have added all the code that you need into one module. If you import this into another workbook you can call it from the macro list or simply use the event code as I have done, that is all you will need to do, like this

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
''/// this calls my macro automatically using the Worksheet event
''/// it's not practical from the addin
  AddControls

End Sub

Select allOpen in new window

mListbox--2-.xlsb
mShowLabel.bas

That creates extra code and I can't get the .bas file to run right.  Still trying to do this with a single block of code inside the module only.

What extra code does it create?

You pasted the extra code that has to be in the workbook before the module code will run.

I have no idea what you mean

You pasted the extra code that has to be in the workbook before the module code will run.

The attached example runs perfectly

I am looking to have all the code in a single module.  

The code prior to your last version consisted of code in the workboook only which works.

The code in your last version works also but contains code in the module AND code in the workbook.

What I am looking for is code that exists ONLY in the module.

The main code is in the module any code in the workbook is simply to call the module,

If you import this into another workbook you can call it from the macro list or simply use the event code as I have done, that is all you will need to do, like this

I have gone over this so many times in previous posts, I have even provided you with an addin to make it easier for you to operate
mListbox--2---2-.xlsb

Sir thank you for trying to make it work with a single block of code from within one module.
I greatly appreciate your effort on this.
The excel sheet in message ID: 42235497, but if the module alone is exported to another file with multiple tabs, it does not appear to function when you run it.

If there is any way that you could look at that, I would greatly appreciate it!

I keep saying that the module cannot function alone. You need to have a button on the sheet or in the Ribbon to call it. The best way is to use a worksheet event, which has to be in the worksheet module not the main module.

Ok, I'll stick with the worksheet only code outside of the module which is the easiest for me to transport to systems that don't allow installations to run.

Thank you for the assistance again.

Glad we got it sorted.