Excel VBA

[prem152003]

I am trying to format a column using my
custom function
Basically Format 11111111101100110094 as 111.11.111.1111.01.100.1.10.0.94

I have created the function.
what I want is as soon the user types the string in a cell in that column and moves to a different cell, i want to invoke the function I created.

Any replies is appreciated.

Thanx

 

[Chattin]

Call could your udf from the Worksheet_SelectionChange event if I've understood you correctly

 

[prem152003]

Is there a way to retrieve the value of a cell just before it loses focus. That is when you hit tab and move to a different cell, the activecell property gives the value of the destination cell , not the value of the cell you are coming from


Thanx

 

[SkipVought]

prem,

As Chattin has suggested, in the Worksheet_Change event, return the value of the function to Target.Value.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[prem152003]

if i move from one cell to another by hitting tab, I
want to display the value of the original cell in a MSgbox.
I could do it SelectionChange event. But how do I
Display the contents of the source location that I am
coming from. Target.value gives me the value of the destination cell
Thanx
Prem

P.S. Could you post the code snippet to achieve this.

 

[SkipVought]

This does not have ANYTHING to do with selecting a different cell, it is the Worksheet_Change event. If you change the value of a range, the Target in Worksheet_Change is the range that was changed. ASsuming that ONLY ONE CELL was changed, the value in the message box is...

MsgBox Target.Value

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi prem152003,

I'm not completely following the thread but in your first post you said:

I am trying to format a column using my custom function
Basically Format 11111111101100110094 as 111.11.111.1111.01.100.1.10.0.94

I have created the function.

You do not need a function just to format a cell. You can do what your example shows with a custom number format.

Select the Cell(s)
Select Format > Cells from the Menu
Click on the Number Tab
Under Category, Select Custom
Under Type, Enter ###\.##\.###\.####\.##\.###\.#\.##\.#\.##

Now the number will appear in your format when the user moves out of it. Please ignore me if you are actually trying to do something more than this.

Enjoy,
Tony

 

[Zathras]

Hey Tony, I don't think the coffee has kicked in yet... There are more than 15 digits in "1111111101100110094" so an edit mask won't work (at least in Excel 97)

prem152003: I assume you have instructed your users to enter an apostrophe first in order to make it a text string otherwise the same 15 digit limitation will affect them too.

The confusion began when Chattin mentioned the Worksheet_SelectionChange event while Skip pointed you to the Worksheet_Change event. It is easy for a beginner to confuse the two. Worksheet_Change is the one you want to use.

 

[TonyJollans]

Hi Zathras,

Off for the coffee pot now

Enjoy,
Tony