Solved

macro / VBA code request for excel spreadsheet

Posted on 2014-04-09
12
773 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
Viewers will learn how to find and create templates in Excel 2013.
Viewers will learn a basic data manipulation technique of unpivoting data in Power Query for Excel 2013 and the importance of using good data. Start with data in a poor structure: Create a table on your data: Unpivot columns: Rename columns: …

809 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