Solved

macro / VBA code request for excel spreadsheet

Posted on 2014-04-09
12
782 Views
Last Modified: 2014-04-10
Hi there

We currently export a report from our company file to excel - I would like to have a macro written which will help make the data useful.

First - excel should have the macros enabled always.

Second - once someone hits the appropriate key - all of the zero values in the spread sheet to not display 0 - it should only display a blank field.

and lastly - the macro / VBA should look at the values in a certain row range (this is a constant and unchanging range), if the value of the cell within the range is zero, then that column should be deleted. this would need to be repeated for the entire range on the row. the constant range in the excel spread sheet is $I$12:$FR$12

Can someone help please

Thank you very much for your help
0
Comment
Question by:Rossco_milkbar
  • 6
  • 5
12 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 39990407
1: Chicken and the egg problem. You can't force that. It's a user settings property. Even if there was code to change this, the code wouldn't run unless the user sets the macro security lower.
2: Only display blank? The cell must remain 0 ?
3: Should be easy enough

Will work on it after the second question is answered.
0
 

Author Comment

by:Rossco_milkbar
ID: 39990418
Thank very much for your help Kimputer!

point 2 - yes value can / should remain zero - just display nothing
0
 
LVL 81

Expert Comment

by:byundt
ID: 39990454
One way to toggle between cells displaying 0 and not is to apply a custom style to those cells. A macro can then change the .NumberFormat property of that style between "#.00;-#.00;0;@" and "#.00;-#.00;;@". This change will apply to an entire workbook.

Another way is to change the DisplayZeros property of the active window. This change will only apply to the active worksheet.
Sub ToggleZeroDisplay()
ActiveWindow.DisplayZeros = Not ActiveWindow.DisplayZeros
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Expert Comment

by:byundt
ID: 39990461
For your third request, you might use a macro like:
Sub DeleteZeroColumns()
Dim i As Range, n As Range
Dim rg As Range
Application.ScreenUpdating = False
Set rg = Worksheets("Sheet1").Range("I12:FR12")
n = rg.Columns.Count
For i = n To 1 Step -1
    If rg.Cells(1, i).Value = 0 Then rg.Cells(1, i).EntireColumn.Delete
Next
End Sub

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 39990572
For your first request, you might consider "crippling" the workbook if macros aren't enabled, and making it fully functional if they are enabled. To do that, you might hide all but one worksheet. That worksheet displays a message encouraging people to enable macros. If macros are enabled, then that worksheet is hidden and the other worksheets are displayed.

Code for accomplishing this must go in the ThisWorkbook code pane. It won't work at all if installed anywhere else.
'The five subs work together to force macros to be enabled when the workbook is opened. If you don't, you'll _
    see instructions on how to enable macros. All other worksheets will be completely hidden.
'Event subs (Workbook_Open, Workbook_BeforeSave and Workbook_BeforeClose) have prescribed names and parameters. _
    You must use the names and parameters exactly as shown--they won't work otherwise. _
    You may have only one event sub of each type in the code pane. If you find yourself wanting two, the code must be _
    integrated into a single event sub.
'The three event subs must be stored in the ThisWorkbook code pane. They won't work at all if installed anywhere else. _
    The two other subs (SpecialSave and ShowSheets) could be installed either in ThisWorkbook or else in a regular module sheet.
Private Sub Workbook_Open()
'This event sub runs when the workbook is opened and macros have been enabled
ShowSheets      'Call the ShowSheets macro to display the desired worksheets
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'This event sub runs when a file save is initiated. It runs before the file is actually saved.
Cancel = True           'Don't save the file after running this macro--it will already have been saved by SpecialSave macro.
SpecialSave SaveAsUI    'Call the SpecialSave macro to save the file (displaying the file selector browser if desired)
ThisWorkbook.Saved = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'This runs when a file close is initiated. The code avoids an endless loop when you close the workbook without having saved it
Dim SaveAsUI As Boolean
If ThisWorkbook.Saved = False Then
    If MsgBox("Do you want to save the workbook before closing it?", vbYesNo) = vbYes Then
        If Not ThisWorkbook.Name Like "*.xl*" Then SaveAsUI = True  'If filename has no extension (i.e. never been saved), then display the file selector browser
        SpecialSave SaveAsUI
    End If
End If
ThisWorkbook.Saved = True   'Don't display the dialog about saving the workbook before closing it
End Sub

Sub SpecialSave(SaveAsUI As Boolean)
'Saves the file and displays the Save As dialog if appropriate
'Hides all sheets except one titled "You must enable macros", then restores the visibility of those sheets
'This sub could have been stored in a regular module sheet, but I put it here for convenience
Dim Sh As Object
Dim flPath As String
Dim celHome As Range
Dim iFileFormat As Integer
If SaveAsUI Then
        'The FileFilter parameter eliminates the double quotes surrounding the default file name in the GetSaveAsFilename dialog, _
            provided that the existing file extension matches the default filter. _
            You get the text for the FileFilter from the File...Save As dialog. Look in the "Save as type" field. _
            Feel free to change the text before the comma. The comma and text following it must match exactly, however!
    flPath = Application.GetSaveAsFilename(ThisWorkbook.Name, FileFilter:="Excel workbook (*.xls),*.xls,Macro-enabled workbook (*.xlsm),*.xlsm", _
        FilterIndex:=IIf(ThisWorkbook.Name Like "*.xls", 1, 2))
            'You don't need the FilterIndex parameter if there is only one filter type. _
                The Iif function is like the worksheet IF function. It defaults the file type according to the current file name.
    If flPath = "False" Then Exit Sub   'GetSaveAsFilename returns "False" if user clears the file name field
End If

Application.ScreenUpdating = False      'Turn off screen updating. Avoids screen flickering. Macro runs faster in Excel 2003 and earlier.
Set celHome = ActiveCell
Worksheets("Enable macros").Visible = True   'Avoids fatal error if enable macros sheet is the last one in the workbook (they can't all be hidden)
For Each Sh In ThisWorkbook.Sheets
   If Sh.Name <> "Enable macros" Then Sh.Visible = xlVeryHidden  'xlVeryHidden worksheets aren't listed in the Format...Sheet...Unhide menu item
Next Sh

Application.EnableEvents = False    'Avoids triggering the Save event macro recursively
    'Excel 2007 and later requires FileFormat parameter in SaveAs method. 52 is .xlsm, 56 is .xls
If LCase(flPath) Like "*.xlsm" Then
    iFileFormat = 52
Else
    iFileFormat = IIf(Application.Version < 12, xlWorkbookNormal, 56)   '.xls file format for Excel 2003
End If
If SaveAsUI Then ThisWorkbook.SaveAs flPath, FileFormat:=iFileFormat    'Save the file using the filename and path chosen by the user
If Not SaveAsUI Then ThisWorkbook.Save          'Save the file using its existing filename and path
Application.EnableEvents = True
ShowSheets celHome             'Call the ShowSheets macro to display the desired worksheets
End Sub

Sub ShowSheets(Optional celHome As Range)
'Hides the worksheet with instructions to enable macros and displays all the rest (with a couple of exceptions)
'This sub could have been stored in a regular module sheet, but I put it here for convenience
Dim Sh As Object
Application.ScreenUpdating = False
For Each Sh In ThisWorkbook.Sheets
    Select Case Sh.Name
    Case "Top Secret", "Confidential"    'These worksheets must remain hidden. List as many as you like, separated by commas.
    Case Else
        Sh.Visible = True   'If you don't need to hide any worksheets, then keep this statement and delete the rest of the Select Case block.
    End Select
Next Sh
Worksheets("Enable macros").Visible = xlVeryHidden     'Hide the sheet containing the enable macros warning message
If Not celHome Is Nothing Then
    If celHome.Worksheet.Visible = xlSheetVisible Then Application.Goto celHome       'If you don't pass celHome, then the first visible sheet is activated
End If
Application.ScreenUpdating = True
End Sub

Open in new window

ForcedEnableMacros.xls
0
 

Author Comment

by:Rossco_milkbar
ID: 39990581
Thank you for your help byundt

The second macro you have provided (thank you) doesn't appear to run - I receive a error message "Compile Error: Type Mismatch" then the debugger takes me to the first line? Have I saved this in the wrong place???
0
 
LVL 81

Expert Comment

by:byundt
ID: 39990587
Could you please post your workbook that demonstrates the problem?

Assuming that you are referring to the code to force the user to enable macros, I posted a sample workbook so you could see the code working correctly.
0
 

Author Comment

by:Rossco_milkbar
ID: 39990603
Thanks - workbook attached - unfortunately the response did not refer to enabling macros - it was to do with the 2nd VBA code - to delete columns if zero.

I note the zero displayed can be hidden through the excel settings.

Thanks for your help and support!
Order-Schedule-Report.xlsm
0
 
LVL 81

Expert Comment

by:byundt
ID: 39990610
Oops!

Both i and n need to be Long.
'Put this code in a regular module sheet
Sub DeleteZeroColumns()
Dim i As Long, n As Long
Dim rg As Range
Application.ScreenUpdating = False
Set rg = Worksheets("Sheet1").Range("I12:FR12")
n = rg.Columns.Count
For i = n To 1 Step -1
    If rg.Cells(1, i).Value = 0 Then rg.Cells(1, i).EntireColumn.Delete
Next
End Sub

Open in new window

0
 

Author Comment

by:Rossco_milkbar
ID: 39990622
That's Brilliant - works perfectly ... But... (sorry) - how do I have the VBA embedded in excel so that the code doesn't have to be imported to the excel worksheet each time we import the data from our company file (at the moment - when I regenerate the report - the code isn't available to run and I need to recopy the code to the worksheet)???

Thanks for your support!
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39990647
If you modify the macro so it references the active worksheet instead of Sheet1, then you can store the code in your Personal.xlsm macro workbook. That workbook is stored in the XLSTART folder, and will be opened whenever you launch Excel. As a result, your macro will always be available to you, and there is no need to copy the code over into the workbook with the newly imported data.
'Put this code in a regular module sheet
Sub DeleteZeroColumns()
Dim i As Long, n As Long
Dim rg As Range
Application.ScreenUpdating = False
Set rg = ActiveSheet.Range("I12:FR12")
n = rg.Columns.Count
For i = n To 1 Step -1
    If rg.Cells(1, i).Value = 0 Then rg.Cells(1, i).EntireColumn.Delete
Next
End Sub

Open in new window

I've attached a sample workbook so you can see what I mean by putting the code in a regular module sheet.

If you don't already have a Personal.xlsm workbook, the easiest way to get one is to record a macro and choose to "Store macro in" in your "Personal Macro Workbook" in the middle field in the macro recording dialog. The macro doesn't need to do anything, and may be deleted once you finish recording it. Once you create the workbook, then paste the DeleteZeroColumns code in Module1 in Personal.xlsm. Make sure that you save Personal.xlsm when you close Excel.
Order-Schedule-ReportQ-28409150.xlsm
0
 

Author Comment

by:Rossco_milkbar
ID: 39992817
Brilliant - you are a legend!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create Consolidation Sheet 3 45
I'm trying to understand this simple vb code! 2 38
SUMPRODUCT and MAX LEN for Structured Table 8 28
Converting time 4 38
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Viewers will learn how to customize the ribbon and quick access toolbar in Excel 2013.
Viewers will learn the basics of formula auditing in Excel 2013.

749 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