Posts by mrfitness

  • Get Current Date In VBA

    I have a calendar in my spreadsheet that returns a date selected to a cell that is active.
    When the user clicks cell D40 in worksheet Surcharges, I use a Worksheet_SelectionChange event to show the calendar. When the user selects a date it returns the date in that cell.
    Problem is I want to limit the user to select a date no more than 90 days from today's date.
    I was having a problem using today() in the vba code.
    When i enter todays date in cell E39 (-today()) and use the code below it works:


    But when I try to use the today() function below I get errors:


    What am I doing wrong? I would prefer to avoid putting =today() in a cell and refer to it. Is that even possible?

  • Sort Worksheets By Name

    Re: Sort Worksheets By Name


    Worked wonderfully! Just had to change the 6 to a 7 :)


    Quote from mikerickson

    If you want to sort by the numeral at the end of the name,


    sort by

    Code
    Val(Mid(SheetName,6))

    rather than

    Code
    UCase(sheetName)

  • Method Of Worksheet Class Failed

    Re: Method Of Worksheet Class Failed


    Thanks for the advice, I did look through the search with the new title. I kind of used an idea of one but put it inside an error handler instead of a seperate sub


    Quote from Dave Hawley

    There are other ways! Which is why I edited your title so you can find them.

  • Sort Worksheets By Name

    Re: Sort Order Of Worksheets By Name


    The 17 sheets at the beginning all have unique names, none of which contain text 'Origin'.
    The sheets that follow the 'Origin' sheets being inputted also have unique names and are unhidden after the macro creates these origin sheets.
    Do you want me to explicitly write down the 17 sheet names? The sheet 'Create Origin Zones' is the one that will always be before the first Origin sheet, if that helps?


    Quote from rbrhodes

    Hi MrF,


    What's the name of the other sheets? In other words do you need a custom sort?

    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]Thanks for the link. I applied the code to my workbook, except the sorting isn't perfect.
    It will sort Origin 49, then Origin 5, then Origin 50 (since I do not have 0's in my single digit origins)


    Quote from Will Riley

    Ignore: sheets


    see


    http://www.ozgrid.com/VBA/sort-sheets.htm

  • Sort Worksheets By Name

    I have a workbook with MANY worksheets.
    The first 17 are static, as well as the 18th sheet on to the end,but there can be many sheets added in between sheets 17 and 18 (up to 56 added) all named Origin 1, Origin 2....Origin 56.
    Users can add these sheets in any order as many times as they want, but eventually the order of the sheets will not be in ascending order.
    I wanted to know how to organize the sheets in order of Origin 1, Origin 2, Origin 3, etc after the user adds new sheets with the macro.
    I can find out how to add it to my current module on my own.
    Thanks :)

  • Method Of Worksheet Class Failed

    Re: Method Of Worksheet Class Failed


    As I state in my question:
    "All the solutions I saw involve saving, closing and reopening the workbook but this interrupts my loop. (It would save my workbook and close it.)"


    Quote from Dave Hawley

    The same question has been asked and answered many times. See your Answers above.

  • Method Of Worksheet Class Failed

    I have a worksheet "Create Origin Zones" - sheet #17 in the array - that has 56 checkboxes.


    Users can click anywhere from 1 to 56 checkboxes, and for each checkbox that is checked, I unhide a sheet "Shp Profile Tmpt", copy it after sheet #17, rename it to "Origin " + checkbox#, and give it a title based on a variable in another sheet.
    Once that loop is done, I then hide the "Create Origin Zones" sheet, but the user can click a button on the new sheet to go back to the 'Create Origin Zones" sheet and add more zones by clicking more checkboxes and re-running the macro.


    Problem is I get that "Run-time error '1004':Copy Method of Worksheet Class failed" when I copy too many sheets. All the solutions I saw involve saving, closing and reopening the workbook but this interrupts my loop. (It would save my workbook and close it.)


    I am trying to have somewhere in my loop, say every time 20 or more checkboxes are checked, its saves, closes, reopens and continues the loop to the next checkbox and repeats the copy and paste and renaming etc.


    Below is my code:

  • Drop Down Cell Link Reference

    I have 6 worksheets that are identical cosmetically. Each has the same drop down box list with the same name (dropdown1), however the cell link on each sheet is different. (ie. sheet1 dropdown1 cell link refers to a sheet 'lookup' cell $K$1, sheet2 dropdown1 cell link refers to sheet 'lookup' cell $K$2, etc.)


    I have the same command button in each sheet that performs the same task so I wanted to keep one module for all 6. Problem is I set a variable to the cell link in the code, and this has to be changed.

    Code
    DropDownValue = Sheets("Lookup").Range("K1").Value


    Is there any way I can set this variable using a property of the drop down list?
    ie

    Code
    DropDownValue = dropdown1.celllink


    I don't want to create 5 more duplicate modules just to change this one bit of code (I only use it this time in the code!).
    If there is no way to get the cell link value of the drop down, could I possibly put a worksheet change event on the lookup sheet to populate a certain static cell with the value of the last cell change?

  • Copy Data From Hidden Worksheets

    Re: Copy Data From Hidden Worksheets


    Ok I kind of get the copy way with hidden sheets, but my macro is a bit more complicated than a simply copy.
    It takes a value in sheet1 and does a find for that value in sheet2.
    Once it finds that value, it moves 10 left from the cell in sheet2 and copies that value back into a certain cell in sheet1.
    How can I code the find function to look at a certain sheet?:

    Code
    Set rcode = Cells.Find(What:=code_find, after:=ActiveCell, LookIn:=xlValues _
                        , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False)

    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]I have attached the sheets I am referring to. ( I still have to attach 1 more small sheet)
    I removed a lot of things to keep the sizes within forum limits (the drop down for you only has 1 option, but there can be over 100!). You will have to merge the 3 sheets into 1 book for the code to work.
    Also, the example in the drop down I selected uses the most lookups. My logic goes that if the row is 7 in sheet 1 and it finds a value in sheet 2, that value will be replicated for the remainder of the column. (so you will find an IF row = 7 in the coding that you can ignore for now)



    Quote from Dave Hawley

    To copy from a hidden Worksheet, use the Copy Method below. Both Sheets can be hidden and the names are CodeNames


    Code
    Sheet1.UsedRange.Copy Destination:=Sheet2.Range("A1")

    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]Last sheet to use

    Quote from Dave Hawley

    To copy from a hidden Worksheet, use the Copy Method below. Both Sheets can be hidden and the names are CodeNames


    Code
    Sheet1.UsedRange.Copy Destination:=Sheet2.Range("A1")

  • Copy Data From Hidden Worksheets

    I have a macro that is grabbing up to 2,400 values from a hidden worksheet. Right now I have the worksheet being visible in order to get the values and paste them into the worksheet the user is on. It seems if I don't make the worksheet visible I get a select method error. I don't want the user to see 2 worksheet screens jumping back and forth like crazy for 20 seconds!
    Below is a snip of my code.Please help! :)

  • Worksheet Change Event Not Firing

    Re: Worksheet Change Event


    Wow I would have never gotten that! I closed and reopened, events are running again. Thanks so much! (I was wondering why nothing was happening!)

    Quote from Bill Rockenbach

    If EnableEvents was shutoff and not turned back by on your code then the Excel Application (all open workbooks and sheets) will not respond to any Event. You either have to enable events with code or quite Excel and then reopen Excel.

  • Worksheet Change Event Not Firing

    Re: Worksheet Change Event


    That looked promising but unfortunately not the solution :(
    I did add it in the code though, good catch!

    Quote from Bill Rockenbach

    Could it be that in your last IF statment block you did not turn Application.EnableEvents back on?

  • Worksheet Change Event Not Firing

    I have a spreadsheet with a table of values in range E5 to T158.


    A macro populates the table by looking up values on other sheets in the book. If the macro finds a value in the lookup for Row 7 of any column (ie E7,F7...T7) it populates the rest of the column with that value (E7 value gets pasted to E8:E158) THEN it protects the cells it pasted (E8:E158).


    If the macro does NOT find a value for row 7, it simply skips it, leaving it blank, and continues to row 8 until it reaches row 158 of each column E to T.


    I want to give the user flexibility with these values. So if the user either deletes E7 or changes the value of the contents in E7, I want to unprotect the cells of rows 8 to 158 for that column.


    I have created a

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)

    in the private module for that sheet below. I thought it was working but it isn't doing anything when I change or delete the value in Cell E7 for example. Please help!

  • Delete & Move Worksheets By Index Number

    Re: Get Worksheet Number


    Thanks that helped a lot. I had to adjust the code slightly as if all the variable sheets were deleted (meaning Create Origin Zones would be 15 and Surcharges 16) I would want to unhide the Create Origin Zone sheets and give it focus. I couldnt just use

    Code
    If Sheets(j).Name = "Create Origin Zones"

    incase the user wanted to delete the first variable sheet but had a couple more after.


  • Delete & Move Worksheets By Index Number

    Re: Get Worksheet Number


    as previously stated the sheets i create have variable names so i cannot select a sheet by name.
    ie a macro creates 3 sheets inbetween Create Origin Zones and Surcharges. Each of these sheets is a copy of a template in the workbook, and they each have a button to execute the macro I posted above. Say for instance I delete the 2nd variable worksheet, I want the focus to go on the first variable worksheet, or if I delete the 3rd I want focus on the 2nd one. Really the user can create up to 56 sheets in between Create Origin Zones and Surcharges so I prefer that the user gets focus of the sheet before the one they just deleted instead of going to Create Origin Zones or the first variable sheet

    Quote from norie

    Why are you using the sheet index?

  • Delete & Move Worksheets By Index Number

    I have a macro that is creating and deleting variable worksheets. Right now when it deletes the sheet, I have it going to focus on the 16th sheet.
    What I want to do is move the active sheet one before (to the left) of the sheet I just deleted.


    Here is what I have below. Sheet "Create Origin Zones" is the 15th sheet...Sheet"Surcharges" is the 16th sheet, but the variable sheets get created in between these sheets, so the "Surcharges" array number changes from 16.

  • Copy Hidden Sheets To New Workbook As Values

    Re: Close Certain Workbook With Vba


    Some great tips thanks...but I am getting an error when I try to create the email

    Code
    Set OutApp4 = CreateObject(" Outlook.Application")

    is highlighted
    error msg is: Run-time error 429. ActiveX component can't create the object
    this didnt happen yesterday. how do i fix that? thanks


    Quote from Dave Hawley

    This should get you there;

  • Copy Hidden Sheets To New Workbook As Values

    I have a macro that copies 2 worksheets of an open workbook "Combined Sales Tool" and saves those 2 sheets in the root of the C drive with a variable name.
    I want to know how I can #1 close the newly created workbook, #2 focus back to the original workbook "Combined Sales Tool", hide the 2 sheets that were copied to the new file, then close the original workbook with (and for example without) saving
    Thanks!
    part of my code below: