Posts by Joe Derr

  • Color Cells Based On Date Conditions/Scope

    All,


    I am at a loss here. I have attached a simplified working copy of my form.


    I have searched every where I can find but keep coming up with sheet based formulas, I am looking for a VBA fix for this.


    The Combobox selects the name of the person, and it populates the textbox with the associated date.


    When the textbox is changed, I am *trying* to determine 3-4 conditions and color them accordingly.


    I have thought to use conditional formatting, but it has to be based off of the selection from the Combobox, so that won't work.


    1. If the checkbox is selected (automatically does based off of reading the cell color) then it will change the interior color to the bright green color (this works)


    2. If the date is within a week from now, then color it yellow.. this doesn't work for me..


    3. If the date is older than the current date, then color it red.. this doesn't work either


    4. If the date is greater than the week out date, then leave it white.. this works, but only because the others don't I assume.



    If anyone can help me out on how to compare dates that would be great.


    Please excuse the messy code.


    Joe

  • Find UserForm ComboBox Value & Transfer TextBox Data To Same Row

    Re: Find UserForm ComboBox Value & Transfer TextBox Data To Same Row


    Dave,


    I did think about it and did review the Anatomy of a Good Thread Title before I posted, that's why its short (one of the suggestions) I do see how your title change drills down, but mine I felt was pretty accurate, ComboBox Row Replacement, I just tried to shorten it too much.


    I will give it even more thought in the future.


    Joe

  • Find UserForm ComboBox Value & Transfer TextBox Data To Same Row

    Hey all,


    My question is, instead of deleting the row, how can I use the combobox to replace that row with the updated info rather than delete and resort?


    I have a combobox that selects names from a sheet, column A and populates itself on Userform activate/initalize. Using the Combobox to select a name, this code below populates all the fields on the form, various text and comboboxs.


    When users hits the update button, it currently finds the row and deletes it, see second code example, but this reaks havoc on various parts of the program, I have to move the combobox and add name textbox's because when it deletes the row, the combobox takes on the next rowsource and then writes that info, rather than the info selected.




    This code is within a command button click sub.



    Then below that, I add a line to the worksheet, dump in the info (which changed to the next row down because of the deleting the row) and then re-write it to that empy line, then resort.


    I know there has to be an easier way.


    Thanks in advance for any help!


    Joe

  • Sort Data With Two Header Rows

    Re: Sort Data With Two Header Rows


    AAE,


    Thanks for continuing to work with me.


    Figured it out.. I was leaving off the ! after the name of the sheet..


    Code
    =OFFSET('EPR Tracker'!$A$2,0,0,COUNTA('EPR Tracker'!$A:$K),11)


    Thanks for your Help!


    Joe

  • Sort Data With Two Header Rows

    Re: Sort Two Header Rows


    Hey AAE,


    Thanks for the help, but still doesn't work, it gave me a run time error of 1004
    "Method 'Range' of Object '_global' failed"


    I put the dynamic name range, that you sited on the sheet, Insert>Name>Define


    Put it in there and named it MyDataRange


    Then but the


    Code
    Range("MyDataRange").Sort


    in my VBA code, on CommandButton2_Click sub and thats when it gave me that Run time error.


    Any idea what I did wrong?


    Thanks!

  • Sort Data With Two Header Rows

    All,


    I have two header rows on a sheet that I need to sort starting on Row 3, Column A.


    The sheet is A-K and rows 1 and 2 are headers


    Row 1 has merged cells of A-F which says "Information" and then G-K "Tracking Section"
    Row 2 is broken up into individual sub-headers in columns, A-K each one having a bit more info for instance:


    Under the "Information" merged header in row 1 there is
    A2 Name
    B2 Work Center
    C2 C/O date
    D2 Due CC
    E2 Due MX
    F2 Date Received


    and G-K in row 2 has different sub-headings.


    I have tried to use my normal code, which is great for one header, but it ends up sorting the sub-header as I call it.


    Code
    ' Sort the list
 Sheets("EPR Tracker").Range("A:K").Sort _
        Key1:=Range("A3"), Order1:=xlAscending, Header:= _
        xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal


    As you can see in the Key1: Range I tried A3 but it still sorted the header, I have tried Header:=xlYes and xlGuess both with out luck


    I have searched the forum and didn't find anything that helped, so trying the post.


    Thanks in advance.

  • Export Worksheet To Word

    Re: Export Worksheet To Word


    Further hunting I have came across this thread:


    http://www.ozgrid.com/forum/showthread.php?t=50378


    This works, if I have a test document in the location, but the program wouldn't be portable. Is there a way to open a word doc from excel VBA, set up some page properties like all word page settings to .5 gutters?


    This works fine if I have this file there in the C: root, again, not portable.


    Is there a way for excel to generate this word doc?


    Here is my code copied and slightly modified for my program:



    Thanks if anyone can solve this problem, you rock!

  • Export Worksheet To Word

    Re: Export Worksheet To Word


    Yes that was one of the first ones I found, before I posted.


    I think this one errored at

    Code
    Set wd = wdApp.Documents.Open("C:\temp\test.doc")


    This document will not be on the users computers, nor can it be placed there, the file is available company wide.

  • Export Worksheet To Word

    I am looking to export a worksheet to Word with VBA.


    I have tried a few posts I found, but none of them worked.


    Any help would be appreciated, I am pulling my hair out.


    Thanks in Advance,


    Joe

  • ComboBox RowSource From Multiple Columns

    I have a userform that has a combobox, right now its filled using rowsource, this limits it to one column. I need it to do more or less a modified column source I guess you call it.


    My sheet starts at V and every 7th column has the data I need to poplute the dropdown, and this can do this over again potentially until the end of the column (I doubt it) but its possible.


    Column V
    Column AC
    Column AJ
    etc.


    So is there a way to start at V and go to each 7th column until there isn't any more filled in?


    I searched the archives and found multiple ideas, but nothing that pulls in data like i need.


    Thanks for any help you can provide.


    Joe

  • OR Conditions In Conditional Formatting

    Re: Conditional Format Error


    I have attached the document, go row 40, Column D you will find the dropdown with the 3 codes I listed above.


    If you select the blank, it should grey out columns H and I via conditional formatting in those columns.



    If you select the 2A676 Column H and I should be white (no color) and J,K and L should be greyed out.


    If you select the 2AX7X then only Column H should be white (no color) and all others should be greyed out.


    The problem is in the conditional formatting for Column I, its erroring because of what you said above... is there a way to fix it?

  • OR Conditions In Conditional Formatting

    Re: Conditional Format Error


    2AX7X is a code I use for training, so the choices in the dropdown are...


    [blank]
    2A676
    2AX7X


    The blank is just a empty space... that part works, its having the 2 at the beginning of the selection is the problem, If I change it to anything but a number, it works.

  • OR Conditions In Conditional Formatting

    I have a problem in my conditional format..


    it checks a cell (which is a data validation dropdown) and depending on what is in the dropdown it makes the cell grey or white.


    =COUNTIF(2AX7X,$D$40)


    the problem is if I replace the 2AX7X with anything else, say Rabbit it will work, I have tried to put in quotes before, after, around the 2AX7X but it still errors. This is the second of three conditions, the first one works, the last two are my problem children. If I can get this one to work, it will work on the third condition.


    Any help is appreciated.

  • Fill Combobox From Multiple Worksheets

    Re: Fill Combobox From Multiple Worksheets


    Dave,


    Thank you for the awesome reply, It works, of course, I took out the last part, as I have divided that sheet with 3 columns into 3 separate sheets with the Data in column A1 of each sheet.


    I have 6 sheets now, all data is in A1.


    I have this code that when you click the drop down it pulls the info to the right of the selected column and puts in in 4 textboxs.



    I have the Sheet passed right now though a series of checkboxs, and option buttons, until your awesome code, but using your code, how can I tell this what sheet to use to look for the info for the 4 text boxes?


    Thanks!


    Joe

  • Fill Combobox From Multiple Worksheets

    I have a combobox that I populate this way:


    Code
    ' Sets Names in ComboBox1 Contents
With Worksheets("Sheet1")
  Set rng = .Range(.Cells(1, "A"), .Cells(Rows.Count, "A").End(xlUp))
End With
With ComboBox1
  .RowSource = rng.Address(external:=True)
End With


    What I have are 4 sheets one of which has 3 columns that I need to combine into one "Master" drop down, is that possible?


    Sheet1 - Column A
    Sheet2 - Column A, B and C
    Sheet3 - Column A
    Sheet4 - Column A


    Thanks for any help provided!

  • Add Days To Date According To Weekday Of Date

    Re: Formula Date Adding Days


    My bad about the code tags.. this is a first post for me that dealt with formula's, I am usually behind the scenes in VBA.


    What this two block thing does is in M7 it has a date you put in. This is a date a test was taken, there are 2 per week.


    So if you put in a test was taken on Monday, it shows the next one due on Wednesday. If you take one on Wednesday, the next one isn't due until the next Monday.


    That part worked. The problem is that if you take one one Thurs-Sun it adds it wrong. I think the tuesday one worked.


    I hope that helps, if not I will post an example file.


    Thanks for the help so far.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Ok, I modified Windy58's suggestion and got what I was trying to do.


    =IF(WEEKDAY(M7)=1,M7+1,IF(WEEKDAY(M7)=2,M7+2,IF(WEEKDAY(M7)=3,M7+1,IF(WEEKDAY(M7)=4,M7+5,IF(WEEKDAY(M7)=5,M7+4,IF(WEEKDAY(M7)=6,M7+3,IF(WEEKDAY(M7)=7,M7+2,)))))))



    Thanks again, sorry I wasn't as clear as I could have been, thanks again Windy58!


    Joe