Posts by Jonathan_Burns

Re: Macro to create time stamps when spreadsheet is open

Thank-you... and sorry!

Re: Macro to create time stamps when spreadsheet is open

freesurf - that is brilliant. Thank you! Though I found that

Cells(new_entry_position, 2)

seemed to work for the before close event...

Thanks again

Jon

I would like to create a macro that is activated when a spreadsheet is opened. The macro would take note of the time the spreadsheet was opened, the changes that are made and then the time the file is closed. These details would be saved onto a separate worksheet in the workbook. Each time the spreadsheet is opened, the macro would create new 'time stamps' and activity logs on the same worksheet.
Can anyone help me please?
Many, many thanks
Jon

Hello,

i'm trying to create a macro (command button based) that filters a field based on the (user defined) contents of a specific cell (B4). The user would type in a word and click the command button - the assigned macro would then filter the defined field to present rows that contain this word.

I have written the VBA script that I thought would perform this (below) - but have got stuck. The issues seems to centre around the following section of code;

Criteria1:=*TargetCell1*

Can anyone help please? Any assistance would be greatly appreciated!

Many thanks

Jon

Sub Macro1()


    Application.ScreenUpdating = False
    
'Set name of VBA Library Index
    VBALibraryIndex = ActiveWorkbook.Name
    
'Set identifier for search cell
    Windows(VBALibraryIndex).Activate
    Dim TargetCell1 As String
    Sheets("VBA Library Index").Select
    TargetCell1 = Range("B4").Value


    Selection.AutoFilter Field:=1, Criteria1:=*TargetCell1*, Operator:=xlAnd
        
    Application.ScreenUpdating = True
    
End Sub

Hello,

I am trying to run a macro automatically when an Excel file is closed - if a particular cell (A1 on the "Data" Sheet) says "Check". If this cell says "Error", the macro should not run.

For the purposes of demonstrating my request, I have added a simple copy&paste command to the macro.

I have tried saving this macro in a separate module and also in 'This Workbook' - but can not get it to function when the file is closed. Please could anyone help me - is the code incorrect? Where should it be saved in order to trigger when the workbook is closed?

Your help is greatly appreciated.

Jon

Sub Workbook_BeforeClose(Cancel As Boolean)


'Sets TargetCell for Ref Error in cell A1 (Procedure should continue only if targetcell says "Check"
    Sheets("Data").Select
    Dim TargetCell1 As String
    TargetCell1 = Range("A1").Value
    
'If cell A1 says "Error", procedure does not run
    If TargetCell1 = "Error" Then


    MsgBox "Data errors on 'Data' sheet, no data Migration will be performed"
    
'If cell A1 says "Check", procedure does run
    Else
    
    Range("A4:B9").Select
    Selection.Copy
    Sheets("Paste").Select
    Range("A4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    End If


End Sub

Re: Protecting Modules

Hi, Thank-you for replying.

There doesn't seem to be any difference after I protect the modules as you directed (even though I checked the 'lock projecty for viewing' box and entered passwords, I can still select and see the VBA code for each module). Do I need to do anything else to activate the protection?

Thanks

Jon

Hello,

I would like to protect the VBA modules I have written in a spreadsheet. Is there any way of password protecting these (as there is with Excel workbooks/worksheets) - such that users can not see the VBA code unless they enter a password?

Many thanks

Jon

Hello,
I have incorporated print/print-preview command buttons/VBA into a workbook with protected sheets. To enable these command buttons to function when the relevant sheet is protected, I have had to add VBA code to unprotect the sheet before generating the print preview, and then to protect it again afterwards. However, the code I have used (see below) prompts the user to enter the protection password, is there any code I can use where I can write the password into the code itself to unprotect the worksheet without the using having to enter the password?
Many thanks
Jon

'Unprotect Sheet
ActiveSheet.Unprotect


'Print preview & cell formatting code


'Protect Sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFormattingColumns:=True

Hello,
I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2".

If I replace WhatToPrint with "Report1,Report2" the print macro works. Can anyone help me to understand why it doesn't work when I leave WhatToPrint in?

Really appreciate your help with this!

Jon

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 05/05/2009 by JW8836
'


'
    
    Dim WhatToPrint As String


    
    
    WhatToPrint = Sheets("Reports").Cells(3, 6).Value
    'sets the variable to equal the contents of cell D3 which contains the formula
    'summarising the print ranges I want to print
    




    Sheets("Reports").Cells(3, 6).Select
    ActiveCell.FormulaR1C1 = WhatToPrint
    ' pastes the variable in cell F3 - just to check that it looks like I want it to
    
    Sheets("Reports").PageSetup.PrintArea = WhatToPrint
    'uses the variable to set print area - this is where it fails!
    'if you replace the variable with the contents of cell F3 the macro will work
    
    ActiveWindow.SelectedSheets.PrintPreview
    
End Sub

Hello,

i have written some VBA code to print a selected print area. However, despite selecting to fit the printed area to 1 page wide by 1 page tall, it still prints over several pages. Please could you help me to correct this?

Range("B82:Z111").Select
    ActiveSheet.PageSetup.PrintArea = "$B$82:$Z$111"
    With ActiveSheet.PageSetup
        .CenterHorizontally = True
        .CenterVertically = True
        .Orientation = xlLandscape
        .PaperSize = xlPaperA3
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With

I really appreciate your help with this

Jon

Re: Print Range Dependent On Cell Content

Thank-you farmertml. The rows I need to hide do not contain blanks. Really, I need to drive this via a macro as the rows to hide will change every day and I want to make the process as automated as possible. In my mind, I would click a command button which would then hide any rows that say "Hide" in column 'A' and then print the remainder. The macro would then un-hide all rows - ready for the next day. I am struggling to write VBA that refers to the content of the cells in column 'A' before determining whether to hide them or not.
Many thanks

Jon

Hi,

I need to write a macro that sets a print range. However, the macro should hide rows where cells in column 'A' say 'Hide'.

Please can you help?

Many thanks

Jon

Re: Delete All Text Boxes In A Spreadsheet/Workbook

Dave, thanks for your reply. The process is complex. Each month, over 20 countries send a one-tab spreadsheet containing financial commentary. These tabs are copied into a 'master commentary' spreadsheet - which then contains all the commentaries. From here, the commentary tabs are migrated, via macro, to a series of files which are distributed to the finance community. As I said before, I acknowledge that this is not the ideal solution, but it is what it is and I am not permitted to change it.
To avoid including historical text boxes (which sit beneath the latest month's text boxes) in the end-files, I need to delete all text boxes in the 'master commentary' workbook before the latest month's commentary tabs are copied over. Do you know if any code exists that can delete all the text boxes in a workbook in one go (regardless of their textbox number)?
Thanks

Jon

Hello,

I have a spreadsheet with over 20 tabs - each containing 6 text boxes containing financial commentary. Each month, the tabs are 'overpasted' with the latest month equivalents. This results in the latest month's text boxes sitting on top of the previous month's. I would like to attach functionality that deletes all the text boxes at the start of the monthly cycle - so the 'overpasting' exercise starts with a spreadsheet that contains no text boxes. I have been unsuccessfull with my attempts because each text box has a unique number - which changes every month. Does any VBA script exist to delete all the text boxes in a workbook or tab?

For reasons that are too longwinded to go into, there is no way around the overpasting set-up as summarised above (I recognise this is far from the ideal solution).

Many thanks for your assistance

Jon

I need to write code to copy and paste a sheet (Sheet D) in one workbook to the back of another workbook (so the copied Sheet D becomes the last sheet in the recipient workbook). The number of sheets in the recipient workbook will change month on month, so the code can not be dependent on pasting after a certain number of sheets - as I have at present;

Sheets("D").Copy After:=Workbooks("Book5").Sheets(3)

I have tried using

Sheets("D").Copy After: End

but this does not work. Please can you help?

Thanks

Jon

Re: Copy And Paste Non-zeros

Thanks Dave,

Your code did everything I asked for - as always, I really appreciate your assistance!

If I wanted the code to paste the results of the source range to a different location in Sheet2, presumably I would need to change the destination address code;

Range(rcell.Address)

Could you advise how to do this as my efforts have not been successful.

Thanks again

Jon

Hello,

I want to write code that copies and pastes values from a range (A1:E5) of data on sheet 1 to the equivalent range on sheet 2 - but which does not copy any cells with zero values.
The data in sheet 1, and the location of zero values therein will change each month.
The attached spreadsheet illustrates the results I am trying to achieve.

I have tried to search for an answer on your website - but without success.

Many thanks for any help you can give me.

Jon

Re: Macro To Delete Range Names

Bob - spot on, thank you very much!

Jon

I have written VBA code which is operated every month. This code deletes several range names in a target spreadsheet, performs other actions - including extracting certain areas to different spreadsheets and then closes the spreadsheet without saving. The range names in the target spreadsheet are required for ongoing use, so can not be deleted permanently.

Is there any code that deletes all range names in one go? At present my code includes the results of recording a macro wherein I delete each range name in turn - creating script over 100 lines long.

Many thanks

Jon

Re: Copy Worksheets Chosen From ListBox

Quote from Dave Hawley

Bad, bad, bad. Use 1 single sheet for ALL related data and then base a PivotTable off it.

Thanks Dave,

I simplified the description of the mechanics in my spreadsheet to help you (and the other VBA experts) understand what I am trying to do. There are actually over 50 tabs in my spreadsheet - with many different structures within. Using pivot tables is not a viable option.

I have created a form with tickboxes such that the user can select any combination of the three common sheets in the spreadsheet to extract to separate spreadsheets, but am now stuck on how to make their selections operate the code. To simplify, the code I want to run is a message box - using the following;

I am using;

Private Sub CommandButton1_Click()
    If CheckBox1 = True Then
    If CheckBox2 = False Then
    If CheckBox3 = False Then
    MsgBox "Only YTD Sheets will be collated"
    End If
    End If
    End If


    If CheckBox1 = True Then
    If CheckBox2 = TrueThen
    If CheckBox3 = False Then
    MsgBox "YTD and Projected Performance Sheets will be collated"
    End If
    End If
    End If


    If CheckBox1 = True Then
    If CheckBox2 = TrueThen
    If CheckBox3 = True Then
    MsgBox "YTD, Projected Performance and Commentary Sheets will be collated"
    End If
    End If
    End If


End Sub

...however, I can not make the message boxes appear correctly as a result of the combination of checkboxes that have been ticked. Please could you advise?

Really appreciate your help with this

Thanks

Jon