Posts by HaHoBe

    Re: Renaming Sheets and Coding


    Hi, S O,


    please clarify:

    Quote

    You can also set the worksheet in memory, then the name doesn't matter


    You would need the correct sheet name in order to get your sample code working.


    If you rely on the codename you must not set an object to that codename as it already exists and may be addressed.


    Sample:
    your code works fine if the sheet is named "Data". You would need to alter the code if soembody renamed the sheet to "Test". Am I correct about this?


    At any case if the codename for the mentioned sheet was Sheet1 the code would work, no matter how the sheet would be named on the worksheet tab.


    Ciao,
    Holger

    Re: Renaming Sheets and Coding


    Hi, oracle259,


    as long as you stay in the very same workbook you should encounter no problem in not using the sheet name like it is indicated on the worksheet tab, i.e.

    Code
    1. Worksheets("Data").Move After:=Worksheets(Worksheets.Count)


    which would move the names worksheet after all worksheets.


    You could look up the codename for the worksheet (assuming this is Sheet1) and use

    Code
    1. Sheet1.Move After:=Worksheets(Worksheets.Count)


    This would move the sheet with the codename no matter what will be displayed on the worksheet Tab.


    The codename for a sheet may only be altered in the VBE via properties or by VBA code.


    HTH,
    Holger

    Re: Worksheet Change not doing anything


    Hi, Red Smurf,


    please try


    where the first check for C12 could be swirtched with teh Intersect line.


    Ciao,
    Holger

    Re: Worksheet Change not doing anything


    Hi, Red Smurf,


    maybe change your code to read


    Although I would prefer to narrow down the range in which to start the procedure by using

    Code
    1. If Not Intersect(Target, Range("D15:D39")) Is Nothing Then


    Ciao,
    Holger

    Re: VBA - copy/paste range of cells every nth column


    Hi, mfields,


    maybe you can work with this code as a start:


    Ciao,
    Holger

    Re: Changing fileformat when saving?


    Hi, stildawn,


    if your actual workbook is in a newer format CF will be cut down to the limitations for workbooks in xls-Format (only 3 conditions) as well as other restrictions reagrding formulas, pivots, and more. As far as I know you can turn the checking off in the workbook you want to save.


    HTH,
    Holger

    Re: Changing fileformat when saving?


    Hi, stildawn,


    maybe like this:

    Code
    1. Sheets("Air").Copy
    2. ActiveWorkbook.SaveAs Filename:="N:\National Share Drive\1 CSU Homepage\CS Interactive Reports\Formatted Reports\" & strClientName & "-Air-" & Format(Date, "dd.mm.yy") & ".xls", FileFormat:=xlExcel8
    3. ActiveWorkbook.Close False


    Maybe consider to use the formatting like yy_mm_dd instead of the format you used.


    Ciao,
    Holger

    Re: I want a macro that will not mess up if rows are deleted...


    Hi, moore.1671,,


    no need to quote the full post.


    The code does not go behind Sheet("Statement") but goes into ThisWorkbook in order for the event to get fired.


    You should copy the code from here and paste it into ThisWorbook as the code you used should raise an exception.


    Ciao,
    Holger

    Re: I want a macro that will not mess up if rows are deleted...


    Hi, moore.1671,


    please wrap your cod ein tags.


    Maybe your code could be simplified to


    because I doubt a loop through a single cell will not really be help.


    Ciao,
    Holger

    Re: Copy Range to new worksheet, consequetively


    Hi, Walter ,


    maybe try it like this:


    Ciao,
    Holger

    Re: Next open row


    Hi, JoeHelpexcel,


    maybe try using a boolean variable to determine whether a user has started to write data and disable the button for all other users for the time until the process is finished (any relational database will deliver this behaviour as a standard without the other disadvantages of a sheared workbook).


    Ciao,
    Holger

    Re: Next open row


    Hi, JoeHelpexcel,


    the code posted here should do okay for a single entry from each control - I don´t get the idea why you are talking about multiple entries being saved on the same row. Can you please elaborate or attach a sample workbook?


    Ciao,
    Holger

    Re: Next open row


    Hi, JoeHelpexcel,


    the cod eyou posted seems to work fine -w emay only test with samples we created and will differ from you original workbook. Assuming the textboxes and ComboBoxes are filled you would need to elaborate if you want to close the workbook (then you can´t quit Excel as the macro stops right when the workbook is closed) or quit Excel as well.



    Ciao,
    Holger

    Re: Next open row


    Hi, patel,


    Excel2013 shows -4162 as the equivalent of xlUp - any explanations from your expertise for a better understanding from my side?


    Ciao,
    Holger

    Re: Check to see if 2 workbooks open and if yes exit sub


    Hi, Jay,


    why exit the sub? In use by you or any other user? If by you/user in the same instance you may like to try


    Ciao,
    Holger