Posts by gmccreedy

Re: File Search Within A Folder

try here...

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

Re: Copy Column & Paste to Last Used Column

where are you defining "strSrch"? How does it know what the search string is?

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: Copying And Pasting Onto Rows

nevermind...this was an inadvertent post

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.

Set rngData = Range("B1", Range("B65536").End(xlUp))
For Each cell In rngData
i = cell.Characters.Count
FindSpc = InStr(1, cell, " ")
Answer1= Left(cell, FindSpc)
Answer2= Right(cell, i - FindSpc)
cell.Value = Answer2
cell.Offset(0, -1) = Answer1


next cell

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.

Re: Combo Box Firing A Worksheet Change Event

combobox is in the sheeet not on userform...but I see what your saying. Will see what it does.

Thanks for the info.

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: Data Validation Ignoring Blanks

use the search feature for this site, and or look at the above links. here is one i believe that can help you.

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

Re: Duplicate Code; Combine Subs

is it a real date or is it a text entry?

Re: Execute A Function When A Cell Value Change

Why not just create a custom format for 11 digit entries (thus forcing the 0 to be accounted for).

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: Button Which Will Import Data

whats going on with your code there...you have command lines outsite the subroutine??

to make a button...open the Forms or Control toolbox toolbars...click on button, add to sheet, right click and assign macro.
Assign Macros in Excel

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.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
application.screenupdating = false
    Cells.Font.ColorIndex = 0
application.screenupdating = true
End Sub

Or

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim ws as worksheet
application.screenupdating = false
    For Each ws In ActiveWorkbook.Sheets
    Cells.Font.ColorIndex = 0
    Next ws
application.screenupdating = true
End Sub