Posts by GuyGadois

    In my form I have the user enter in the current date in Textbox1. My program is designed only to work in 2009 so I want to check to make sure the 1) the date is in 2009 and 2) textbox1 is not empty. If it is empty then it displays a message box with "Not a Valid Date. Please Enter Date as MM/DD/YYYY. Date has to be in 2009" - this doesn't work. Second, if the date is outside of 2009 it is to display a meeage box saying "Date has to be in 2009".


    Here is my current code which is not working.



    Any help is greatly appreciated.


    Guy

    I am looking for a formula to change a condition based on the time of day. Essentially, before 1pm I would like the formula to be:


    =((I12/100)*25)/D12


    After 1pm I would like the formula to be


    =((I12/100)*25)/B12


    I tried various ways with "=if(now()> ..." formulas to no avail because of the way Excel handles Now().


    Any help is much appreciated.


    Guy

    Re: Hiding Chart Sheets If Users Disables Macros


    Well, I spoke to soon. The code below actually doesn't work. If the user enables macros then all worksheets are displayed except the chart worksheets. I am obviously not putting Dave's recommendation to work correctly. Any ideas what I should do so that both worksheets and chart worksheets are hidden if macros are disabled (except Splash worksheet) and shown if macros are enabled?


    Thanks you,


    Guy


    Quote from GuyGadois

    Dave,
    Thanks for your advice. This is what I ended up with and it seems to work fine...


    Re: Hiding Chart Sheets If Users Disables Macros


    Quote from Dave Hawley
    Code
    Dim wsSht as Object
    
    
    For Each wsSht In ThisWorkbook.Sheets


    Dave,
    Thanks for your advice. This is what I ended up with and it seems to work fine...


    I am using Reafidy nice bit of code to hide worksheets if the user disables macros. The problem, though, is that it doesn't work for Chart Worksheets. How should I adjust this to accommodate charts?


    My spreadsheet requires Macros to run properly. If a user 'Disables' macros then nothing really works. So, I have put the following code that is supposed to hide all the sheets except one if the user disables macros. The problem is that sometimes it works and sometimes it doesn't. I really can't figure out why it sometimes works and sometimes doesn't. In any case here is the code:



    This is located in "ThisWorkbook"


    'HideAll' is supposed to trigger if they disable macros. Here is HideAll:


    Re: Re-order Columns Based On Cell Value


    Quote from Dave Hawley

    IF you mean moving, saying Column "A" to another position, it's not possible to change the order of Columns.



    If you mean sort, see Mikes post above.


    Thanks for your input. This code seems to be working fine. I appreciate everyone's help.


    Guy Gadois


    Code
    Sub Sort_Columns()
        Columns("A:E").Select
        Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
            DataOption1:=xlSortNormal
    End Sub

    Re: Re-order Columns Based On Cell Value


    Quote from Dave Hawley

    How do these numbers (1-10) correlate to the order of columns?


    I would have a form that the user gets to order the columns 1-10 with 1 being the first column. When the user submits the form then the values in row A would change to what the user entered and then the columns would re-order.


    That is what I envision, at least.


    Guy

    Is it possible to re-order entire columns based on cell values? For example, row A gives values of 1 thru 10 to the first 10 colums. I would like a macro that reorders the entire columns based on that value. Is that possible?


    Guy

    The following code breaks at the end when I try to select a specific worksheet. Here is what I have so far:



    Note above where I say "This is where it breaks". What I am attempting to do is select the "wb" workbook and select the "2008 sheets" within it. What is the best way to do this? I get a 1004 error "Select Method of Worksheet Class" error.


    Thank you,


    Guy

    I have a macro that makes the current worksheet range equal a range in a different worksheet. The problem is that when the range is change is retains the worksheet name in it. For example:


    I want it to say:
    =IF($C16='Admin'!$B$4,'2008 Log'!$K16,0)


    But it says:
    =IF($C16='[FILENAME.xls]Admin'!$B$4,'[FILENAME.xls]2008 Log'!$K16,0)


    How do I avoid this? Here is the code I use to make the ranges equal:


    Re: Copying Ranges From Another Workbook


    Dave,
    Thanks for your suggestion. I tried it but it is still having the problem where if the sizes of the named ranges are different then it doesn't work. These named ranges incorporate the entire row. So, January on the original sheet could be rows 3:36 while on the destination sheet it could be only 3:17 but needs to increase in row size to accommodate the additional rows added by the user in the original sheet when the macro takes place.


    Any suggestions on how to do this?


    Thanks,


    Guy


    Quote from Dave Hawley
    Code
    With wb.Worksheets("Admin").Range("January_Data")
            .Clear
            wkbDataFile.Worksheets("Admin").Range("January_Data").Copy _
            Destination:=.Cells(1, 1)
        End With

    [hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I am not sure if I am being clear (which is probably the case), but the code seems to only be copying and pasting to the new sheet instead of expandaing the named range to the same size and then pasting. Any ideas on how to accomplish this?


    Guy

    In my macro I bring over a named range from one workbook to the other. In fact I do it for about 100 named ranges. This is a part of the code that seems to work (thanks to the good people at Ozgrid):



    The problem is I get the following warning when it runs for each named range I am copying over.


    "A formula or sheet you want to move or copy contains the name 'NAMED RANGE", which already exists on the destination worksheet. Do you want to use this version of the name?"


    Is there any way to avoid this?


    Thanks, Guy

    I have the following code:


    Code
    wkbDataFile.Worksheets("Admin").Range("January_Data").Copy Destination:=wb.Worksheets("Admin").Range("January_Data")


    In some cases the range "January_Data" won't exist and the macro breaks. Is there a check I can have the macro run through first to ensure the range exists in wkbDataFile.Worksheets("Admin") before going through with the .Copy?


    Thank you,


    Guy

    Hello everyone. I have a two workbooks that looks like this (in an oversimplified way for the example):


    Workbook One:
    January
    1
    2
    3
    4
    5
    February
    March
    April
    May


    Workbook Two
    January
    1
    2
    February
    March
    April
    May


    In both accounts I have named ranges for the rows under January to the row before February. The named ranges are the same "January_Data" (since they are different workbooks) and is all the data between January and February. I have a macro that takes Workbook One "January_Data" and copies it to the Workbook Two "January_Data". The problem is, though, that when is copies the "January_Data" over to Workbook Two it doesn't just take the 5 rows and replace the 2 rows in Workbook Two and move "February" heading down. It actually writes over it. What I want to have happen is that after the macro ends the following is the result.


    Workbook One:
    January
    1
    2
    3
    4
    5
    February
    March
    April
    May


    Workbook Two
    January
    1
    2
    3
    4
    5
    February
    March
    April
    May


    The workbooks should match. Again the problem is that instead of making the named ranges the same in size it just pastes it over and it looks like this:


    Workbook One:
    January
    1
    2
    3
    4
    5
    February
    March
    April
    May


    Workbook Two
    January
    1
    2
    3
    4
    5
    May



    Here is the code I am using. Any help is appreciated!


    Guy





    Code
    Private Sub CommandButton2_Click()
        Import_Data_Form.Hide
    
    
    End Sub

    Re: Reset UserForm Textbox Controls


    Quote from Reafidy

    If the form is unloaded and you then use the .hide command then you are actually reloading the form and then hiding it, so try:



    Ugh, that makes total sense. I did as you said and that did the trick. Thank you and Dave for your help!


    Guy

    Re: Reset UserForm Textbox Controls


    Quote from Reafidy

    Please clearly explain when you want to clear the textboxes and when you want the data loaded into them, it is unclear what you want.


    Hi, I want the data loaded into the form when the user initiates the macro to show the Form. If the user clicks either CommandButton or the X at the top right of the form I want the values of the form to be removed so that when the user shows the form again it is not showing old data but rather new, "fresh" and accurate data.


    Does that make sense?

    Re: Reset UserForm Textbox Controls


    Quote from Dave Hawley

    Depends on when you want the TextBoxes cleared. It works fine under any of the UserForm or Control Procedures.


    Dave, this is where I placed it (a few places to test it out) but it doesn't seem to work. I know I am making a dumb mistake, I just don't know where. Any help is appreciated.


    GUy