Posts by cytop

    Re: Display/Show Column Headings In ListBox

    A ListBox will only display headings when the RowSource property is set to a range on a worksheet. The headings will be taken from the cells directly above the first row of RowSource.

    It is not possible to add headings to the ListBox using code.

    The only alternatives are to add labels above the listbox (Total PITA) or write the recordset to a worksheet (starting in row 2), use that as the Rowsource and then add the column names, using code, to the row above the list.

    Re: When combobox empty nothing should happen

    If ComboBox2.ListIndex > -1 Then
        '// remainder of code
    End If

    The ListIndex property returns the 0 based Index of the selected item. If nothing is selected then ListIndex returns -1.

    Re: Click on save button on webpage through excel vba

    There is no update, the topic is posted in the wrong forum.

    You posted an additional question in your other thread ("Web automation...") which was not related to web automation. That is what I meant by "The above is nothing to do with "Web Automation using VBA". Please start another thread in the proper forum". The question related to email automation - this is the email sub-forum, so only questions about emails here please.

    On a social note you may be interested to know the emoticon you've used above can be seen as a taunt. The definition of 'taunt' is:


    1. Verb: Provoke or challenge (someone) with insulting remarks.
    2. Noun: A remark made in order to anger, wound, or provoke someone.

    Not exactly a way to encourage replies.

    Nothing to do with the last comment, but as this thread is posted in the wrong forum I am closing it. You are welcome to start another thread in this forum for any email related questions but please ensure you understand exactly what you are putting in the message.

    Re: VBA code for summarizing data


    Relevant comment from the other site regarding the amount of work required


    If you need it automated ASAP, you'd better start working on it. This isn't a code writing service, but feel free to ask about any specific issues you run into regarding code you have written

    Re: Userform with combo boxes and assigning macros


    But now it's not practical...

    It wasn't from the start, either. But that's just my opinion.

    It should be possible to have just a couple (at the most) macros to do everything but you need to upload a copy of your workbook to see the full picture. Only sample, anonymised, data is needed.


    Re: Userform with combo boxes and assigning macros

    Are the procedures 'Macro1', 'Macro2' & 'Macro3' (or whatever their real names are) similar...? If so, post an example of 1 (if not too big).

    In the meantime, a little rationalisation of the Change and Initialize procedures:

    Not that it's any better than your existing code, just shorter.

    Re: Time left to reach SLA

    That's the thread on THIS forum you copied most of the the post text from and you didn't start that thread (why you would do that is a mystery to start with) - but you have posted this on another forum. Link please.

    Re: Combobox in Userform

    You need to understand what this is doing...

    ActiveCell.Offset(0, i).Range("AH1").Select

    Assume A5 is selected and i contains the value 10

    ActiveCell.Offset(0, i) refers to cell K5

    K5.Range("AH1") means that K5 is 'considered' to be the 'home' cell and AH1 is the cell offset 34 columns (Column AH is column 34) from that.

    In other words 'ActiveCell.Offset(0, i).Range("AH1").Select' refers to Column AW in this example.

    It's beyond me why you would use such a convoluted range reference but there's not enough detail in your post to answer further.

    Re: skipping columns in an aray

    I can't see how it would work correctly.

    For example, if rngTarget contains 5 rows then looping from 0 to 5 will actually process 6 rows (0,1,2,3,4,5 - and I don't mean to say those are row numbers, but are a reference to the array element).

    Still, if it works...

    Re: skipping columns in an aray

    Then it's related to the Array lower bound as I mentioned...

    Try (again, untested. Need a copy of the workbook if this fails).

    Redim Preserve rcArray(0 To rngTarget.Rows.count - 1, 0 To rngTarget.Columns.count - 1)

    Re: skipping columns in an aray

    I notice you're using 1 as the lower bound of the array rather than the default 0. The ListIndex property (The number 'assigned' to each item) of a Listbox is 0 based...

    First step, comment the 'On Error Resume Next' and see what happens,

    You shouldn't turn off error handing while developing (You shouldn't turn it off at all unless you are going to handle specific errors yourself) - it covers a lot of evils that will make themselves known at the worst possible time, like when you think you're finished and are demo'ing to anyone above your pay grade :)

    If the issue is still not obvious, copy of the workbook please - just a sample of anonymised data needed.


    Re: Combobox in Userform

    Don't use the 'OFFSET' in the Select statement. That will select the cell 37 (or so), plus whatever value i contains, columns to the right of the current cell - which sounds just about right for Column BS, if the cursor is in Column A.

    After saying that, I've no idea what the code is supposed to do.

    Re: skipping columns in an aray


    Typed freehand, untested.

    Re: Sort by last 3 characters and by status

    Won't a 'normal' 2 level sort do that?

    Code generated by the Macro Recorder while sorting on columns 'Mnenomic' & 'Status' in your sample

    Re: MS Access import text files and assign records to a relationship table $70

    This is a 'free' forum, if you wish to pay for a solution you should post in the Hire Help forum (I will move this thread if you agree).

    One of the conditions of posting in the Hire Help forum is that you pay 10% of your offer price via PayPal to [email protected]. The remainder is paid to whoever takes the assignment, upon delivery.

    Only those members who have shown their ability with Excel & VBA can accept assignments via HH and I wouldn't worry too much about no one accepting this, there are quite a few members who watch the HH forum like hawks...

    You will also need to upload a copy of the file with some representative, sample data, preferably anonymised. This is so anyone considering the project can accurately gauge the amount of work needed and verify the sample data is sufficient to illustrate the requirement.

    This thread will be locked until you decide if it is to go in the HH forum and payment of the 10% - but just to keep others out for the moment.

    Re: Loop Through Files in Folder and check DateLastModified

    Of course, me saying "it will work" was on the same basis as my code - untested, but in general it would work (except for the logic errors :))

    Never mind... going back to my preferred method using native VBA functions, the following will list all files in a directory where the month is not equal to the current month. Optionally, you can pass it a File Spec ("*.XLS?") to limit the checking to just Excel workbooks, for example.

    It has expanded a little, but that's only to do with listing the 'invalid' files. It will list up to 15 'invalid' files. If there are more, it will append an "and x other(s)..." message to the output

    You call it using something like

    If Not AllFilesDatedThisMonth("c:\temp") Then Exit Sub

    where you pass the directory to check.

    If you want to limit the checking to a certain file mask, then use

    If Not AllFilesDatedThisMonth("c:\temp", "*.XLS?") Then Exit Sub