Posts by gmccreedy

    Re: Copy Column & Paste to Last Used Column


    no, I understand for the most part. I thought I had seen the error in the script, but after i looked at it again, I was incorrect.


    Let me ask you this...


    the search criteria you have, is this a list in your workbook? if so, you can do a for loop for each of the search strings, through each of the used ranges on your first sheet, then when criteria matches, go on to the next and so on.


    If it meets one criteria, does that mean the rest of the search strings are ignored and you move on to the next column? Or do numerous conditions need to exist?

    Re: Cut Text From String At First Space And Paste Elsewhere


    VBA is what I needed.


    I ended up figuring this out. Not sure if its the best way to do it, but it worked.



    thanks to all your replies though

    I have a text string in a cell. it is comprised of a series of numbers and text. i would like to "cut" the first part of the string starting from the first space and paste in the cell to the left.


    For example.


    range("b11") has the following: "This is my example"


    I would like to perform the following:


    range ("A11") would equal "This"
    Range ("b11") would equal "is my example"


    (note the space has been completly removed as well)


    Thanks in advance for any help.


    (Also...the first word never has a set length)

    I have a series of combo boxes of a sheet in that reference the same list. I want to be able to restrict or stop a duplicate entry from these boxes on the sheet. Restrict or stop can be as simple as a msgbox.


    I do not want to use data validation as the list. (I need to utilize the autocomplete feature that the combo box has and data validation doesn't).


    I have attached a sample workbook of a sample list and a few drop boxes. The linked cells for each combo box are in column E of the same row.


    Any thoughts or solutions are much appreciated.

    Files

    • ComboBox.xls

      (25.6 kB, downloaded 189 times, last: )

    Is it possible for a combo box selection, linked to a cell on the same sheet to fire a worksheet change event? I can't seem to find a way to do it.


    Not sure if there is a property that needs to be set or am I out of luck here....

    Re: Button Which Will Import Data


    I am not sure I follow you....


    You want to select one cell....from what I see you already are.


    "Range" does not necessarily need to be a mulitple selection...it is what it is...whether its the entire sheet of cells or just one.


    Is this what you are asking???


    BTW...the VBA help menu is pretty good. It will provide you with great definitions and examples of common terms.

    Re: A Survey Of Spreadsheet Users


    Graham,


    I think the biggest problem with some companies is that they feel that a resource or tool needs to cost money in order to be worthwhile and productive. The "how can something so inexpensive be so good" mystic is a cancer in the business world that eludes me....


    Keep it simple, use what you got and make it happen.


    Should be Excel's motto.

    Re: A Survey Of Spreadsheet Users


    Carl,


    I feel I can provide some insight as to my success with Excel.


    My office (prior to my employment) had been doing there timesheets and billing by hand. YES BY HAND!!! they did utilize Quickbooks for the actual generation of invoices and such, but it was still all being crunched with a pencil and calculator...


    Upon stumbling on this site one day...I realized the true potential of Excel...and became reborn as a obsessive compulsive stickler for streamlining everything. i find myself making spreadsheets for everything...and have forced my co-workers and staff into utilizing them.


    Long story short...the timesheet spreadsheet I was able to create (customized for my compny) has saved (on average per month) approximately eight to ten thousand dollars (US)!!! I was able to figure this out by monitoring how much time people actually spent doing there timesheets and billing and balancing that with billable time. truly amazing...


    In all honesty....if it wasn't for this website and some of its wonderful members...my company would be losing about 100k per year in billable time.


    Just thought i should share that with you.

    Re: Selecting A Folder


    by default, I think the open file reverts to a the host folder location of the file on the save as.


    However, if you want to force a location, you could code as stated above, or define a variable path as a range within the workbook and call on it same as above then execute the getfilename command

    Re: Add-in Opens Workbook It Was Created In


    Like JIUK said...it sounds like you might be calling the macro from the original workbook as opposed to the addin.


    Try deactivating the addin, then click "your button". If the code still executes, then you know the path is not set to fire from the addin but from your original source workbook.


    Nevermind...it appears that we were posting at the same time...lol.

    Re: Protecting Cells In Shared Workbook


    your very welcome. best of luck with your employee search.


    FYI...I am sure you can create a log file of users using a very easy if then statement that could verify who your "bad apple" is.


    Of course you would have to not use the code posted above for a while so that the user could make the font color changes. I have done this in the past and it worked like a charm.

    Re: Protecting Cells In Shared Workbook


    Nevermind...I am an idiot...use either code below.


    Code
    1. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    2. application.screenupdating = false
    3. Cells.Font.ColorIndex = 0
    4. application.screenupdating = true
    5. End Sub


    Or

    Code
    1. Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    2. Dim ws as worksheet
    3. application.screenupdating = false
    4. For Each ws In ActiveWorkbook.Sheets
    5. Cells.Font.ColorIndex = 0
    6. Next ws
    7. application.screenupdating = true
    8. End Sub