Home

Macros and VBA

62 Conversations

Latest Activity

Custom List Message Item

I'm running into a challenge -- I need to create a spreadsheet that runs with VBA on both Windows and Mac. 

 

When I create a new spreadsheet in Windows, I can go ahead and add the "Microsoft PowerPoint 16.0 Object Library" - add my code and it runs fine. 

... Read More
7 Views
0 Reply

I have a sheet into which I record new customer enquiries in consecutive rows.

 

Formula in the same row in subsequent columns pick out keywords and construct an appropriate reply from concatenated strings. Because the number of enquiries can vary from a ZE

... Read More
71 Views
4 Replies
The easiest way is to use a Filter on a column that has your delete criteria, then Go To (F5) > Special > Visible cells only > Delete rows > turn off the filter. All of w... Read More

I have a table that I want to color the font red in an entire row IF the contents in Row N# contains the word EMPTY.

 

I would like to do this in VBA, thoughts?

33 Views
1 Reply

Hi Mike,

 

Why don't to do with simple conditional formatting?

Hello,

 

I have a problem with using the VBA Application.Dialogs(xlDialogPrint).Show function in Excel Mac 2016.

When I use the VBA Code the decimal and thounsandsseparators are changed from the "german" cell format to "us". When I use the print funktion fro

... Read More
36 Views
0 Reply

Hi There

 

I've created a macro that copy some lines and delete values manually entered. I then entered a row above the lines being copied and now the macro has deleted al my formulas - which took me two days to develop.

 

Is it possible to undo actions execu

... Read More
46 Views
2 Replies
Sorry, but if you didn't create a backup, your work is gone. When VBA code makes a change on the grid, it clears the undo stack. Unfortunately, Excel doesn't have a speci... Read More

Close without Saving and then reopen.

Restore from an AutoSaved version.

In any case, the Undo thread gets fouled up, so always be careful when working with macros that hav

... Read More

Excel 2010:

Am creating a worksheet for a coworker who enters equipment calibration dates and other details for all the calibrated equipment in our labs. He currently has one workbook with multiple worksheets. Some equipment is duplicated among the workshe

... Read More
140 Views
6 Replies

I think I understand what you're saying. Ingeborg is right though, structured this way is backwards.

 

If the data you're looking up is in a Table, then you can reference i

... Read More

Hello,

 

I'm not sure I fully understand the data architecture, but to me it seems to be backwards, or the wrong way around.

 

A logical architecture would be to have a list

... Read More

I have a product sales spreadsheet which is generated monthly. I also have a rebate report which is also generated monthly. I inserted an array formula into a new column (column E) in the sales spreadsheet which checks each row of data find a match of the

... Read More
59 Views
1 Reply

Try using something like this.  Not sure how you plan on inserting rows....but this will get you the array formula via VBA.  You'll need to define the last row of your da

... Read More

Good morning,

 

I've programmed the next instruction:

 

aux1 = Application.WorksheetFunction.Norm_S_Dist(Sheets(b).Cells(4, (i * 3)), True)

 

In a windows PC. When I tried to run in my mac (Excel 2011), it doesn't work. How can I solve it please?. Thanks.

Read More
66 Views
2 Replies

Hello,

 

as far as I know, the function Norm_S_Dist is not available in VBA for Excel for Mac 2011. So, if possible, you should use the older version NormSDist:

 

=Applicatio
... Read More

Update:

 

I have attached a link to be able to download the 2 files needed to accomplish what I'm looking for.

 

Using the R&M form, when you pull the data out of the GL Detail file, I would like it to also verify invoices on R&M to the GL by comparing the ac

... Read More
96 Views
4 Replies

It may help to upload the workbook with non-sensitive data.  I can roughly see what your code is doing, but it would greatly help to see the data structure in each of the

... Read More
I had to paste the MACRO on here as the file wouldn't upload. I can manual supply the files if needed. The above macro is pulling the totals for each of the account codes... Read More

I created a command button with a VBA code to print invoices for clients.  Yesterday, when I clicked the button it worked, but now when I click the button nothing happens.  I have tried all the fixes.  I have deleted the button and reset it.  I have gone

... Read More
143 Views
2 Replies

Are you sure it's running? Put your cursor in the sub routine and hit F8 to step through it. If nothing happens, events are probably disabled. Restart Excel, or, in the I

... Read More
I'm not sure but it might be worth checking the Trust Centre for Active X and Macros

I have created a spreadsheet where the sheet 1 is a form to be filled in for Quality Control. I have created a "Save" button that will use the 'copy and move' function to create a copy of the first sheet at the end of the same workbook. I need all the com

... Read More
102 Views
6 Replies

Hi @horsteadjim,

 

Do this for Copying Current Sheet and Paste it to End of Sheet.

 

Sub Macro1()
a = Sheets.Count
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Sheets
... Read More

HI

 

How can I hide or block a macro so that nobody else can edit or change it? I need to make a macro enabled workbook available to many users.

51 Views
2 Replies

VBA is well known to not be secure. Even if you put a password on your VB Project, it's relatively easily hacked. Project protection has not been changed in several years

... Read More
You can lock your macro code via the following steps: Alt + F11 to open the VBA window Tools > VbaProjectProperties > Protection Tab

I have four sheets I want excluded from my macro (Renaming based on cell). This is my first round with macro, so I'm not sure what to input. My sheets are: Directions, Tips & Tricks, Home, Bubble Kids. And here is my macro:

 

Sub RenameTabs()
'Updateby201406

... Read More
83 Views
5 Replies

Try out this code

 

Also I'd encourage you not to reference cells directly in your VB code (e.g. B2)  and instead used Named Ranges  (e.g.  Go to Formula > Define Name > ca

... Read More

Hello Friends,

 

I need to create a Macro in MS excel 2010 so that I can divide a file into several files based on the filtering of a particular column.

ie, One column of the excel file will contain several entries , some of which are common - so all the com

... Read More
189 Views
7 Replies

Ok, here is the code I used. The initial ranges are assumed and could just as easily be made dynamic. It should work pretty fast.

 

The big assumption here, besides the ran

... Read More

Dang, ok. I'll write it up again. It's super quick because it uses sort instead of filtering. I have some assumptions regarding the ranges, so while I'm writing this up,

... Read More

Did anyone see my other post? It was a fairly long piece of code. I posted it a couple hours ago. I'm not going to be happy if it's actually lost, spent a good chunk of t

... Read More

Hi there,

 

This is a typical request. There are several ways you can do this. Personally I don't like the filter method because it's really slow - at least compared to sor

... Read More

Here is sample code to filter values on Sheet1 by a person's name.  In this example the names are Matt, John and Jared....  If you paste this code into the attached workb

... Read More

Hello all,

I often use =Mod(row),2)-1 to apply alternating row colors, where the first row does not receive the formatting.  I also have a simple macro that I use to run that type of conditional formatting.  The user selects the area that needs the formatt

... Read More
115 Views
8 Replies

You can still use conditional formatting, no need for VBA, just adjust your formula. Something like this...

 

=MOD(ROW($A2)-ISEVEN(ROW(A$2)),2)

 

This assumes that your condi

... Read More

Something like this will work:

 

Sub Test()

    Dim myVar As Long

    myVar = ActiveCell.Row
    
    If myVar Mod 2 = 1 Then
        MsgBox "I'm an odd number!"
    Else
... Read More
Best Response

Now i am using window 10 and home and SME office 2013.

When i move on to the interface of visual basic, some of the command keys become question marks?

Did anyone get this problem before? How can i clear the problem.

My problem likes the pic.未命名.png

Read More
33 Views
0 Reply

Hello everybody,

I'm facing a big challenge, I have to make everything as simple as possible. The problem is handling the fresh data. I've made a sample file, how it should all look like. I'm gonna explain my problem now.

For the purpose of this request the

... Read More
73 Views
2 Replies

Hi there,

 

I would recommend thinking about additional years as well as months. Also, since the PivotTable is based on the Table on Arkusz1, and they don't refresh automat

... Read More

Hi there,

 

I would recommend thinking about additional years as well as months. Also, since the PivotTable is based on the Table on Arkusz1, and they don't refresh automat

... Read More

Hello all,

 

I have run into a strange issue where we have a workbook with macros in it that has worked in Excel 2007 without issue.  

 

Upon moving to Excel 2016, the workook is fine if the user manually Enables Content each time the workbook is opened.  

 

Bu

... Read More
54 Views
1 Reply

Enabling content manually allows Excel to do processing before your code is run.
The Workbook_Open event sometimes may get triggered before Excel is finished with its hous

... Read More

Occasionally my macro hangs.  I believe it is in this code but since the only way to spot the hang is to task manager kill the job, I can't be sure.  Is there a way to return to my code after a time out period attempting to access a web-page?  The query r

... Read More
36 Views
1 Reply
You may want to try changing .BackgroundQuery = True to .BackgroundQuery = False

I am a restaurant owner/operator and trying to create a good tool for our chefs to put out a neat and organized daily prep list for the crew at the start of each day.  Right now a lot of this is still done w/ paper and pen but with mulitple languages, thi

... Read More
91 Views
1 Reply

I believe you want a listbox not a combobox.  See the attached file for an example.  You'll need to insert a code module and link the shape to the macro called AllTogethe

... Read More

I have created a spreadsheet to QC Maintenance Plans. I would like to save each one as another sheet in the same workbook. I have created a button and need to assign a macro to that button. Can anyone help me with the code to save that sheet as a sheet wi

... Read More
74 Views
1 Reply
Sub Test()
    For Each Sheet In ThisWorkbook.Sheets
        'Copy Entire Sheet and Paste ss new sheet in same workbook
        Sheet.Copy After:=Sheets(Sheets.Count)
   
... Read More

Hello to everybody,

i have a problem. I've created a new excel component and i have registered it in my excel to use. The component works well.

The problem is when i try to copy a seleceted area with key combination CTRL+C, excel run my component and doesn'

... Read More
32 Views
0 Reply

Hi guys!
I have a big problem, I try to make macro that is gonna to make a pivot table(example in file) and then divides the sheet with table and send it by outlook(every table to specified person).
Pivot has to be just like in the example. I tried to do it

... Read More
39 Views
0 Reply

Somewhere between V1701 and V1705 a change was made to excel which affects when CutCopyMode is returned back to false.  You can see this using Excel normally if you copy something and see those crawling ants but then press the Delete key.  Previously this

... Read More
80 Views
2 Replies

Hi @Ross Edwards,

 

Send me that excel file and that macro. I will try to solve it.

Read More

I have a sheet with multiple push buttons and when I copy the information to a new sheet additional buttons over and above the ones on the sheet are created.  Can any one help?

61 Views
2 Replies

Hi,

 

Try to paste that information as values only, or eliminate that buttons from the copy area before copy.

Hi @M Neil Cameron,

 

Try to right click the sheet and make copy of that.

 

Read More