Solved

macro / VBA code request for excel spreadsheet

Posted on 2014-04-09
12
763 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 80

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
 
LVL 80

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 80

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
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.

 
LVL 80

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 80

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 80

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Welcome to part one of a multi-part tutorial series, VBScript for Windows System Administrators.  The goal of this series is to teach non-programmers how to write useful VBS code to automate their environment, and perform tasks faster, and in a more…
Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
Viewers will learn what comprises a theme in Excel 2013, as well as how to customize them.
Viewers will learn the basics of formula auditing in Excel 2013.

746 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

10 Experts available now in Live!

Get 1:1 Help Now