Posts by TheGlovner

    Re: ADO Crash on successive calls to SQL Server


    I had a similar issue once where the code altered the users environment inputs (PROD/ACCT/SYST/INTE/UNIT) into the appropriate server name (DSA/DSAA/DSAS/DSAI/DSAU).


    The environment was entered byref to the code that altered it to the appropriate server name, so when it made the second call it went through the same code but never had to override say SYST to DSAS as it was already set to DSAS from the previous SQL call.


    I've no idea why it was failing, or why this fixed it, but all I did was pass in a temporary copy of the environment string (i.e SYST), which was overridden to the server name, but wouldn't overwrite the original environment string, so when the second call was made it passed SYST from the input variable into the temp variable which was passed to the code to run the SQL.


    EDIT: I should add that in theory changing the passing of the parameter to ByVal should have achieved the same result, but I hadn't written the SQL part of the VBA so didn't have access to alter that code directly, it was also an add-in referenced by a number of sheets so doing it this way avoided the regression testing impact that would have been inherent with changing the SQL VBA Code.


    And it worked.


    As I say no idea why, but if it sounds similar give it a bash.

    Re: Macro to help match and sort data


    Try this out.


    You'll need to alter some references to suit your data setup.


    Module Code:




    Class Module Code:



    Class Module was also reamed to OutputClass.

    Re: VBA Code for renaming worksheets


    So basically you want to rename SheetName to be [SheetName & ValueFromColB] when some sort of flag is set in Column E?


    So what is column E being set to in order to prompt the change to be made?


    Then it's just the case of evaluating that value with an if statement which encapsulates the code that is used to rename the sheet, so something like:


    Code
    If ValueInColumnE = TheValueThatPromptsTheNameChange then
            'Code To Update Sheet Name In Here
    End If

    Re: Divide rows into multiple rows if a cell has more than 1 "row"


    Can't download the sheet, at work waiting for some tests to run.


    Something like this should help you though:


    Code
    'Takes the input string and splits it into an array using a Line Feed as the delimiter
    Public Function SplitLineBreaks(ByRef strInput as String) As Variant
        SplitLineBreaks = Split(strInput, vbLf)
    End Function


    So you need to feed in the value from the cell as the strInput and it will return an array with a minimum size of 0 to 0, or 0 to X based on the number of line breaks found in the string, with each element representing the string from the start/line break to the next line break/end.

    Re: Auto fill unknown values from available data: day, month and year.


    So I've taken a bash at solving your problem.


    I don't think the answer is to try and use code to solve it, seems like over engineering to me.


    To get round the idea of overtyping formulas and having to use code to address the formula being lost I would strip out the Input and keep this separate from the calculation.


    So in the above example you have an input area (Columns B, C & D) and a calculation area (Columns F, G & H).


    If you enter a yearly value, this is used to populate Column H, calculate Column F (Input / 52) and calculate Column G (Input / 12)
    If you enter a weekly value, this is used to populate Column F, calculate Column H (Input * 52), then Column H is used to calculate Column G (Yearly / 12)
    If you enter a monthly value, this is used to populate Column G, calculate Column H (Input * 12), then Column H is used to calculate Column F (Yearly / 52)


    This way you don't have to worry about overtyping or replacing formula.


    Usually good practice to separate your Inputs from your working.

    Re: Auto fill unknown values from available data: day, month and year.


    [TABLE="width: 500"]

    [tr]


    [td]

    Row\Column

    [/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [td]

    F

    [/td]


    [td]

    G

    [/td]


    [td]

    H

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td]

    [TABLE="width: 108"]

    [tr]


    [td]

    WeeklyInput

    [/td]


    [/tr]


    [/TABLE]

    [/td]


    [td]

    MonthlyInput

    [/td]


    [td]

    YearlyInput

    [/td]


    [td][/td]


    [td]

    WeeklyCalc

    [/td]


    [td]

    MonthlyCalc

    [/td]


    [td]

    YearlyCalc

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Category

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    =IFERROR(IF(LEN($B2)>0, $B2, $I2/52), "")

    [/td]


    [td]

    =IFERROR(IF(LEN($C2)>0, $C2, $I2/12), "")

    [/td]


    [td]

    =IF(LEN($D2)>0, $D2, IF(LEN($C2)>0, $C2*12, IF(LEN($B2)>0, $B2*52, "")))

    [/td]


    [/tr]


    [/TABLE]

    Re: Auto fill unknown values from available data: day, month and year.


    Are you using the year to calculate both Month & Weeks?


    i.e.


    YearlyTotalFromMonth = MonthlyTotal * 12
    YearlyTotalFromWeeky = WeeklyTotal * 52
    MonthlyTotal = YearlyTotal / 12
    WeeklyTotal = YearlyTotal /52


    So if someone input a monthly total it would calculate the yearly total and then use this result to calculate the weekly total?

    Re: How to Automatically remove blank rows when selecting from a list (DV)


    Explanation is a touch confusing.


    Is your data (Hotel, Mall & Bank List) on different worksheets? I'm assuming it must be since you quote the range as being in column A in each case so they would all overlap on the same sheet.


    If they are different worksheets, then that code is only designed to work based on a change to your current worksheet. If you wanted to affect the visibility of rows on other worksheets you would have to reference them explicitly.

    Re: macro - to create new sheets from a data validation list


    This should create the sheets and name them for you, unclear what you want to do from there, I assume you want some data taken from other sheets and added to them.



    Couple of objects/references would need renamed in your project for it to work:


    "wsSourceData" is the code name I've given to the sheet where my test data was to make sure it worked.
    "Source_Customer_List" is the named range on the wsSourceData worksheet where my test Customer Names were held.


    Hopefully that gets you started.

    Re: Change color of final result based on raw data given


    Another option is of course using Styles rather than fill colours which incorporate a number of properties into a single command, where if you just change the fill colour your font colour may not show too well against the new background colour.


    If you want to use styles rather than background fill colour the property for the range is just ".style". The value is a string representation of the style name.

    Re: Change color of final result based on raw data given


    Can't download the sheet as I'm at work.


    But you should be able to use the ".Interior.Color" property of the range/cell to get the fill colour of the cell.


    Not sure what the shade of red is that you want to validate against but you can easily get this by following your code through to where it picks up the cell colour.


    Once you know the serial number for the colour you should easily be able to validate an If Statement against it.


    Something like:


    Code
    If lngOriginalFill = RedColourSerialCode then
         cell.Interior.Color = RedColourSerialCode
    end if

    Re: macro - to create new sheets from a data validation list


    So you have 50 customers in a range (which you use to prompt the dropdown options) and you want to use each customer to create a single sheet per customer.


    Is that correct?


    Is there are good reason for creating 50 spreadsheets? I'm a little confused on the purpose.

    Re: Is this Possible - Select ComboBox value, the give answer


    Quote from RBLearning;799566

    Thanks, I'm playing around.


    Just noticed that you want it displayed in a textbox rather than on sheet. So still a similar solution but something like:



    I've just thrown that together on textpad so it may have some bugs but hopefully it gives you an idea.


    I'm assuming you have the combobox code already, but this would require it to be set as a function rather than a subroutine so the selected value can be passed back to the dteSelectedDate variable.

    Re: Exact Multiply in Excel


    I'd guess that your initial result was actually 8.515, but the cell was formatted to only show 2dp. So it rounds it for display (8.515 rounded nearest to 2dp is 8.52) but still retains the full precision 8.515 within the cell.


    As Rory pointed out, rounding the original formula actually converts the cell contents to 8.52 rather than just for display purposes.


    So, it was always multiplying accurately, it's just you weren't aware of the true input to the multiplication.

    Re: Counting colors and updating formula


    To expand on what KjBox has already said.


    I have a module contained on a stand-alone add-in workbook that I can either reference (for users that have the same drive access), or just pickup and drop into the project I'm working on (for users that don't have access to the same drives).


    It contains a number of different functions that I use all the time (GetLastPopulatedRow, GetLastPopulatedColumn, GetUserID etc), but it also has two public subroutines just called ProcessStart and ProcessStop.


    The process start basically switches automatic calculations to manual, sets EnableEvents, ScreenUpdating and DisplayWarnings to False.


    The process end switches all these things back (I do have a little bit more going on depending on whether I want to force certain application settings or I return the settings to the users application settings as they were pre-processing.


    Saves me the hassle of having to do this every time though.

    Re: open an excel file and insert a row with data on it then close it back


    I'll try and take a look at the issues when I have a moment.


    As to how it can help with the writing of the data, basically you would use the add-in to request write access to the file, if it can't give you write access then it would return an error message confirming that write access isn't available at that time (you may want alter the message to confirm to try again in a moment).


    My assumption is that it should only be getting opened by this process so people shouldn't be sitting in it for long.

    Re: Simpying Repetitive Macro Codes


    If the row range changes for each one but the processing on that range stays the same then you should set the range accordingly from the checkbox click routine, then pass this along with the checkbox to the other generic functional routine.