Posts by peerogel

    Re: Moving between columns (dynamic range?)

    Thanks for all your help. The vba works incredibly fast.

    Cells 78 and 79 contain array formulas that return military times. I kept trying to put 'if' formulas on cells 81 that would return the '1-13' values but I could not get it to work, I think it was because of the array formulas.

    In a cell (80) formatted as minutes [m], the formula returns the time difference between cells 78 and 79. So the first formula would return 0 if the times are the same in both 78 and 79.

    The error was just in the first if statement, like Pike stated. So in the first 'if' statement instead of running the same formula, i changed it to if they are the same values then return 1.

    If ws.Cells(oRow, i).Value = ws.Cells(iRow, i).Value Then

    I cant figure how to move between columns applying an "if" formula. I finally managed to move between columns applying a value.

    But I have been trying to figure how to apply an "if" formula between the columns and I am totally lost.
    This is the formula:

    If Round(((ws.Range("AO79").Value - ws.Range("AO78").Value) + (ws.Range("AO79").Value < ws.Range("AO78").Value)) / 2400, 5) = 0 Then
       ws.Range("AO81").Value = "1"

    So I came up with this but it does not work at all. I get a run time error 13. Thanks in advance for any help.

    Re: Update Range with Forumulas

    I figured out a work around. I inputted the dates on the same sheet and using the same formula and dragging it down copied them correctly. Thanks for the help.

    Re: Update Range with Forumulas

    The Range A1:B50 is a range for a bar graph. 'Qtr 1 2016'! Row 3 (B3:QO3) contains a calendar and row 4 (B4:Q4) contains an average of employees selecting date ranges in the calendar.
    A1 has the values from 'Qtr 1 2016'!D3 and 'Qtr 1 2016'!J3 these values fill out the Axis in the graph. B1 will display the bar values from 'Qtr 1 2016'!D4 and 'Qtr 1 2016'!J4.

    So 'Qtr 1 2016' B3 has the first value and 6 columns to the right is the second value that goes in cell A1 on a separate sheet.

    Re: Update Range with Forumulas

    Thanks but it does the same thing. It increases the rows instead of columns.

    on B2 and B3 it will give me
    =TEXT('Qtr 1 2016'!D4,"mm/dd")&" - "&TEXT('Qtr 1 2016'!D4+6,"mm/dd")
    =TEXT('Qtr 1 2016'!D5,"mm/dd")&" - "&TEXT('Qtr 1 2016'!D5+6,"mm/dd")

    instead of

    =TEXT('Qtr 1 2016'!K3,"mm/dd")&" - "&TEXT('Qtr 1 2016'!K3+6,"mm/dd")
    =TEXT('Qtr 1 2016'!R3,"mm/dd")&" - "&TEXT('Qtr 1 2016'!R3+6,"mm/dd")

    I am trying to update range A50:B50 with formulas. I was wondering if there is a way to make this easier or if I have to do one by one. This is what I have so far. Thanks in advance.

    Re: Add Values to Cascading Dropboxes Solved!!

    Solved!! Got it Working!!! Thanks for the help!!

    strSQL = "INSERT INTO tblManufacturer(Manufacturer,CategoryID) " & _
                      "VALUES ('" & NewData & "','" & Me.cbCategory.Value & "');"

    Re: Add Values to Cascading Dropboxes

    So the first Dropbox goes to the table called tblCategory in the field Category.

    So this is what I used.

    So, the second combobox goes to a second table called tblManufacturer, field Manufacturer. How can I modify the code to get the value of the first combobox, look for its ID and paste it on the appropriate field on the Second table (CategoryID).

    Thank you.

    Re: Add Values to Cascading Dropboxes


    I spent hours googling different cascading combobox issues, but never saw the On not List option. It worked perfectly on my first ComboBox but I'm having issues with the second and third. It will ask me, if I want to add the new value and after I select Yes, it will ask again... the value never gets inputted into the table.

    I tried adding a value to the table manually and if I don't add the "linking" value it will not add the new line, so I am thinking that is the issue. For the second combobox, I am thinking that I need to add the value like I did with the first combobox, then take the value of the first combobox get its "ID" (linking value) and put it in the second table. Im not good with vba, so now im googling how to do that and hoping that will be the issue.

    I am creating an inventory database. I found myself inputting several duplicates and got the idea to use cascading dropboxes. I managed to create the cascading dropboxes, but realized that I can only use the values on those tables. Is there a way to make it so the dropdown has the edit list items option?

    If not, I was thinking of creating a button that would bring up a query and add the items from there. However, I cant get it to work.
    If I do a query and link Category and Manufacturer it will add the values properly to the tables. But that's where I get stuck. I have a third table and that is the one that is giving me the issues.
    It keeps telling me that I cannot change the record because the records are related.

    Thanks for any help. I attached a sample table.

    Re: Reuse same vba code with different cell values

    Thanks for the quick responses. I am a total noob, hopefully i can manage to use this. The reason i was using MSForms the way i did was because i made a time sheet with 20 comboboxes per day. The code i posted would only be for the first sunday.

    I recently learned (thanks to iwrk4dedpr ) how to use MSForms to reuse the same code simply by calling it and reassigning the comboboxes. This helped me a million, the vbas in my userforms shrinked.
    [Blocked Image:]

    I am making a new userform and the vba code I want to use is exactly the same and i will be using MSForms. However, i was wondering if there is something similar to MSForms to change the values i want in the activesheet cells. This is what i have.

    This is how i want to call it.

    I want to have different values in the comboboxes if the value in Range A1 is different.

    if checkbox2 is false and the value in A1 is 1
    instead this
    objctrl1.Value = vbNullString
    i would like this
    objctrl1.Value = Format(ActiveSheet.Range("X1")+100, "0000")

    if checkbox2 is false and the value in A1 is 2 then
    objctrl1.Value = Format(ActiveSheet.Range("X1")+200, "0000")

    Could this be done by doing something similar as MSForms? Or would i have to rewrite the code several times and calling different codes for the values i want if a1 is 1 or 2or so forth?
    Thanks for your time and help.

    Re: combobox_change to multiple comboboxes. (Solved)

    Thanks a bunch. I was missing that (me.combobox) portion after the call.. This will reduce my code by several hundred lines.. Once I find a procedure that works for me, I repeated it over and over. Once again thanks for all the help.

    Re: combobox_change to multiple comboboxes.

    I moved it to the userform but i still get a not defined compile error and it highlights
    Sub newroutinehere(ctrlcbo As msforms.controlbox)

    i am trying to attach the file.. but in the file manager all i see is sending request to ozgrid.. but nothing gets attached.

    thanks for all the help.

    Re: combobox_change to multiple comboboxes.

    Sorry for being a total noob. I keep getting a "sub or function not defined" compile error. this is what i have done so far.

    In the class module I typed

    And in the userform I have

    Private Sub ComboBox13_Change()
    Call newroutinehere
    End Sub

    The error highlights Call newroutinehere.

    Thanks a million for the help.

    Re: combobox_change to multiple comboboxes.

    Sorry, Im still not good with vba.. So, If in the original procedure I say.

    With combobox1

    How do I make it so it applies to the comboboxes I want? or do I just remove the "with combobox"?

    Thanks for the quick response..

    I have the following procedure in a Userform.

    I was wondering if there is a way to assign the aforementioned procedure to several comboboxes. The number of the comboboxes is not continuous (ex. combobox13,14,15 then jumps to combobox20,21,22). I have 70 comboboxes that i want to give this procedure to. I am new to vba and my procedures are typically long.. I think my vba code is getting way to long and has began to crash. I was going to write the same procedure 70 times with all my comboboxes but after my 13th combobox my excel sheet crashes and goes through the recovering procedure. I did post this same question in another forum but after getting a suggestion of sub-classing and loops Mr Ross has not responded. While looking for loops I found a class module by Mr. Mike Erickson ( that sounds like it could help me.. But I have no idea how to modify it to do the aforementioned. In the example I attached the list consist of just and alphabet letter and a number but the reason I adjust the with is because the list is longer than the actual combobox. I only attached the procedure to one combobox (#13) it is the first box on the first sunday, right column. I hope I am not getting in trouble for asking this question again in this forum. (I could not attach the file... Ill try again later). Thanks for the help.