Working with active selection in Private Sub Worksheet_Change

[Aussiexile]

Hi all, i hope someone here can give me a hand.

i need to be able to send a selection of text to the onchange function.

in coloumn C i have a range on ID's to enter and have them split up into seperate columns. now i have code that does this if you enter them individually but i need a way to do this if say i copied them all from another file.

i would also like to be able to do this with the title function.

i relise i will have to remove

Please Login or Register  to view this content.

to accomplish this

im quite new to this style of programming so any advice on ways to shorten the code would be much appreciated as well

thanx in advance

PS im using excel 2003 if thats of any relevence

Please Login or Register  to view this content.

[protonLeah]

Say you have a title in F1, then, check for "*":

Please Login or Register  to view this content.

returns a non=zero if "*" is found.
Use the Worksheet Function PROPER() to change the case of the title:

Please Login or Register  to view this content.

[Aussiexile]

I need to be able to preserve some words in CAPS if needed which is why i split the title into an array.

ie
this is an ABREV in the title


now ABREV needs to stay in all caps

so i split the string....look for the * and dont format any word that is in front of

[MarvinP]

Hi Aussiexile,

I wondered first why you put the code behind the sheet instead of in a module. Then I wondered why you didn't have some functions in your code. It looks like you can program.

I believe the Worksheetfunction.Trim built into Excel will compress double spaces to single in a string. I'm not sure the Trim() in VBA will do this. This might ward off errors for blank lArr.

I too thought code like protonLeah would work.

Please Login or Register  to view this content.

This is more a matter of style than anything. We all have to read our own code.

I'd consider making the Title routine a Function and call it with your text and it would pass back the proper stuff. If this function was in a Module you could use it from anywhere instead of just from behind your individual sheet.

The SDN routine could also be moved to a module. My concern is that if you are pulling the data from outside excel it would once again work with any sheet.

I guess I don't understand why you put the code on a Change Event behind a sheet.
I hope this makes sense.

[Aussiexile]

I want the code to run whenever those cells are changed which is why i have placed the code where it is.

Also in the title function the abreviations are not constant, it could be HSEC in one title or ABCDEF in another. i needed a way to that the user could specify which word/s was to be left unformatted.

some clarification on the title routine

Original: THIS IS A TITLE FROM A FILE FROM ABCD TO EFG
what its ment to look like: This Is A Title From ABCD to EfG
so user enters : THIS IS A TITLE FROM A FILE FROM *ABCD TO *EfG <this line would be cut and pasted from original file. or entered into the cell in all lowercase by the user except for the abbreviations

the code then splits the string and lookes at each element in the array for the * and then doesnt format it.

make sense???

[MarvinP]

Yep the code looks good. I think it is more versatile in a Module instead of behind the sheet.

You could keep the onchange event on the sheet and call the code that lives in the module. This would be my objective. This might be personal preference too.

[Aussiexile]

how would i do that??? and is there a way to make that work on an active range......eg cells A1 to A5 already have data and i paste 5 numbers into the spreadsheet and then the code will run for A6 through A10??

[romperstomper]

For example:

Worksheet code:

Please Login or Register  to view this content.

Normal module:

Please Login or Register  to view this content.

[Aussiexile]

that looks to be exactly what im looking for.

Thanx everyone for your help will update if i have any issues


EDIT: Tested it and that solution is perfect. Thans once again for everyones help