Can a formula be written in excel or VBA to perform the "text to column" function ?

Bob

Wow ! That is slick except for one glitch.  If I add the second delimited amount for the labor column in the inventory items ie:

Easy Elegance Rose    ,24.99  , 34.00

It only brings the labor over (the second figure 34.00)  and not the materials, and it puts it in the material column.

Maybe I was not clear that there would be two dollar figures,  the first (24.99) is the material itself and the second (34.00) is the labor to install.

Lynn

Hi Lynn,

Here is a revision

Hi Bob

I am sorry to be so late in getting back to you.  My sister was in a serious bicycle accident and so I have needed time off to help with her medical stuff.

The revision works well but the  work sheet is a 2 page worksheet and the second page does not do the text to columns function you set up.

the menus seem to be working as far as I can tell at this point ( I wont know more until I get the real data into all of them.

Thanks

Lynn

I am sorry to hear that Lynn, I hope she is well on the raod to recovery.

That code is currently tied to just thw one sheet, what is the other sheet in question.

Bob,

The work sheet that it is tied to is a 2 page worksheet,  just scroll down and a second page emerges below page 1.

The code only works on page one and not on page two .

Thanks

Lynn

Sorry to take so long, my aptop failed (again!).

Try this

Good Morning Bob

It almost works!  ;)   

Ok  we are dealing just with the second page, the first page works perfectly .  

The first drop down menu with the catagories of product such as bulk, garden roses etc works fine.

the second dependent drop down set of menus which contains the actual product for each catagory only seems to work for garden roses and shrub roses the rest of them dont show any product to pick from .  they are just blank.

Thanks

Lynn

...

Bob,

Yahoo...I think it works.  Let me fiddle around on it for a couple days and see what happens.

I know I will have a couple of questions .

I will get back to you in the next day or so.

Keep your fingers crossed!!!

Thank you so much

Lynn

Good Morning Bob,

Sorry I have not gotten back to you.  Had to go to CA to take care of my sister after that awful bicycle accident she had.  But am back and i have one little glitch.

I needed to add a third page to the worksheet which was only two pages before.  The same problem has cropped up with page three as with page 2.  The menus work but the text to column feature you built in does not so the material price and the labor price do not extend out to the proper columns.

Attached is the improved 3 page worksheet.

If you could take a look I would be greatful!

Lynn

Lynn,

There is worksheet event code associated with that sheet, in the worksheet code module, that controls that action. The code has cell reference at two points, one where the price and loabour data is cleared on a change of Category

   
    If Not Intersect(Target, Me.Range("B15:B42, B66:B93")) Is Nothing Then

    ElseIf Not Intersect(Target, Me.Range("C15:C42, C66:C93")) Is Nothing Then

I just changed these to

    If Not Intersect(Target, Me.Range("B15:B42, B66:B93, B117:B143")) Is Nothing Then

    ElseIf Not Intersect(Target, Me.Range("C15:C42, C66:C93, C117:C143")) Is Nothing Then

respectively to accomodate the new page.

If you add another, you can extend in the same way.

I hope your sister is doing well and on the raod to recovery.