Posts by Emmy

Re: VBA - what has been cliked?

Thanks for thinking on this some more. This is the button that I'm using and refer to above (apologies if I wasn't clear). This gives me my desired behaviour in that it just closes the active workbook but leaves Excel open, with the Personal workbook still hidden. The only issue is that users can still click the normal close button.

Not to worry - I guess I'll need to make it up to the user...

Re: VBA - what has been cliked?

To try and explain briefly - in Excel 2013 if you have the Personal workbook hidden and close another workbook using the normal close button Excel closes too. I know that this is a change Microsoft made to the behaviour in Excel 2013 but I'm trying to find a way round it. I noticed that when you click the Close Window button which I've added to the QAT it only closes the active workbook and leaves the Excel session open with the Personal workbook still hidden - this is the behaviour I want. So I was hoping there was a way of distinguishing between clicking the QAT button and the normal close button but apparently not.

Another route I tried was to use the Application close workbook event but came across other issues...

Anyway I appreciate your time but I will give up for now. Thanks anyway!

Re: VBA - what has been cliked?

Thanks Cytop for looking at this.

I'm trying to make the user use the Close Window button I've added to the QAT rather than the close button in the top right hand corner.

Re: VBA - what has been clicked?

Anyone able to help with this?

Hi,

In VBA, is there anyway of determining what "button" has been clicked e.g. a command on the Quick Access Toolbar?

Thanks,

Emmy

Re: Auto incremental purchase order numbering in Excel 2003

Apologies for the double post - I'm having internet/posting problems.

Re: Auto incremental purchase order numbering in Excel 2003

I'm sorry I don't know what to suggest. It works fine for me when I save the workbook as something else - PO number stays static.

Also, since there is no BeforeSaveAs event on Excel 2003 I see no way of stopping the problem you encounter when opening the main workbook but don't create a sub workbook as it were.

I'm out of ideas and give up - sorry I couldn't help.

Re: Auto incremental purchase order numbering in Excel 2003

I'm sorry I don't know what to suggest. It works fine for me when I save the workbook as something else - PO number stays static.

Also, since there is no BeforeSaveAs event on Excel 2003 I see no way of stopping the problem you encounter when opening the main workbook but don't create a sub workbook as it were.

I'm out of ideas and give up - sorry I couldn't help.

Re: Auto incremental purchase order numbering in Excel 2003

I'm not sure why you're having problems - are you changing the name of the workbook when you are saving?
This is how it should work:
Open Purchase Order form workbook (PO number goes up by one)
Save the workbook as something else e.g. Purchase Order 1
When you open Purcharse Order form workbook again PO number will go up by one again
and when you open Purchase Order 1 PO number shouldn't change.

Re: Auto incremental purchase order numbering in Excel 2003

Change the capital F in Form to lower case f, save and closeworkbook and try again.

Re: Auto incremental purchase order numbering in Excel 2003

The workbook is called Purchase Order Form not Sales Order, also need to save the "template" - try the following code:

Private Sub Workbook_Open()
    If ActiveWorkbook.Name = "Purchase Order Form.xls" Then
        Sheets("Sales Order").Range("G4").Value = Sheets("Sales Order").Range("G4").Value + 1
        ActiveWorkbook.Save
    End If
End Sub

Re: Copy a frequently changing column to different columns each time

I'm using Excel 2003 so I imagine that's the problem. Can you try saving it as a macro enabled workbook?

Re: Copy a frequently changing column to different columns each time

How do you mean? When I click the button a third time I get data in Column E. What version of Excel are you using?

Re: Auto incremental purchase order numbering in Excel 2003

Again, I would suggest uploading the workbook.

The code I posted assumed the following:
You have an Excel workbook called Sales Order with a worksheet within it called Sales Order as well. When you open this workbook you want the value in Cell G4 on worksheet Sales Order to go up by one. You will then save this workbook as something else. When you re-open this workbook the value in Cell G4 shouldn't change. Am I correct?

Re: Copy a frequently changing column to different columns each time

You could try the following - probably not a very elegant solution but should work none the less.

Re: Copy a frequently changing column to different columns each time

Why don't you post an example?

Re: Auto incremental purchase order numbering in Excel 2003

Why don't you upload the xls - I'm having difficulty following this.

Re: Auto incremental purchase order numbering in Excel 2003

Sorry - please look at my latest edited post - I was a little hasty when copying code from elsewhere.

Re: Auto incremental purchase order numbering in Excel 2003

Would the following code work?

Private Sub Workbook_Open()
If ActiveWorkbook.Name = "Sales Order" Then
Sheets("Sales Order").Range("G4").Value = Sheets("Sales Order").Range("G4").Value + 1
End If
End Sub

Re: Adding 100 rows automatically when column text changes

You should use code tags when posting code.

How do these not work how you want?