Format Cells #14

I'm trying to figure out a way (if there's one) to enter a number into a 
blank cell and have it automatically multiply that cell by 25.4 (essentially 
converting a number from in. to mm.), keeping the new number in that same 
cell:  enter 2 into A2 and it converts it to 50.8 and A2 now shows 50.8.

Is this possible?

Thanks.
0
MAWII (9)
4/27/2005 7:47:19 PM
excel.misc 78881 articles. 5 followers. Follow

8 Replies
334 Views

Similar Articles

[PageSpeed] 16

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    With Target
        If .Column = 1 Then
            .Value = .Value * 2.54
        End If
    End With

ws_exit:
    Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"MAWII" <MAWII@discussions.microsoft.com> wrote in message
news:4B213E8B-C0C0-4E9E-AA79-B2D4DBDEA048@microsoft.com...
> I'm trying to figure out a way (if there's one) to enter a number into a
> blank cell and have it automatically multiply that cell by 25.4
(essentially
> converting a number from in. to mm.), keeping the new number in that same
> cell:  enter 2 into A2 and it converts it to 50.8 and A2 now shows 50.8.
>
> Is this possible?
>
> Thanks.


0
bob.phillips1 (6510)
4/27/2005 8:14:06 PM
That's pretty cool.  I've got it working for the range of cells I need it to 
work for.  Is there a way to keep the cell empty if there's nothing in it?  
If you accidentally type something into a cell and delete it, the cell 
returns a 0, and that will ultimately corrupt my future calculations.  I just 
need empty cells to stay blank.  Thanks!

-Mark

"Peo Sjoblom" wrote:

> Not by formatting, do you want this in any cell in the sheet or just one cell?
> Assume the latter and A2
> 
> It would take an event macro so if someone else is using it they have to 
> enable the macro when they open the workbook, if not it won't work
> Riight click the sheet tab where you want this, select view code and paste in
> 
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Intersect(Range("A2"), Target) Is Nothing Then Exit Sub
> Application.EnableEvents = False
> Target.Value = Target.Value * 25.4
> Application.EnableEvents = True
> End Sub
> 
> press alt + Q to close the VB editor
> 
> now type an inch value in A2 and press enter
> 
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> "MAWII" wrote:
> 
> > I'm trying to figure out a way (if there's one) to enter a number into a 
> > blank cell and have it automatically multiply that cell by 25.4 (essentially 
> > converting a number from in. to mm.), keeping the new number in that same 
> > cell:  enter 2 into A2 and it converts it to 50.8 and A2 now shows 50.8.
> > 
> > Is this possible?
> > 
> > Thanks.
0
MAWII (9)
4/27/2005 8:49:37 PM
Hi Mark,

Slight amendment then

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    With Target
        If .Column = 1 Then
            If Not .Value = "" Then
                .Value = .Value * 2.54
            End If
        End If
    End With

ws_exit:
    Application.EnableEvents = True
End Sub

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"MAWII" <MAWII@discussions.microsoft.com> wrote in message
news:B4997AED-664B-4E22-9EDE-4BC7FBD731C6@microsoft.com...
> That's pretty cool.  I've got it working for the range of cells I need it
to
> work for.  Is there a way to keep the cell empty if there's nothing in it?
> If you accidentally type something into a cell and delete it, the cell
> returns a 0, and that will ultimately corrupt my future calculations.  I
just
> need empty cells to stay blank.  Thanks!
>
> -Mark
>
> "Peo Sjoblom" wrote:
>
> > Not by formatting, do you want this in any cell in the sheet or just one
cell?
> > Assume the latter and A2
> >
> > It would take an event macro so if someone else is using it they have to
> > enable the macro when they open the workbook, if not it won't work
> > Riight click the sheet tab where you want this, select view code and
paste in
> >
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > If Intersect(Range("A2"), Target) Is Nothing Then Exit Sub
> > Application.EnableEvents = False
> > Target.Value = Target.Value * 25.4
> > Application.EnableEvents = True
> > End Sub
> >
> > press alt + Q to close the VB editor
> >
> > now type an inch value in A2 and press enter
> >
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "MAWII" wrote:
> >
> > > I'm trying to figure out a way (if there's one) to enter a number into
a
> > > blank cell and have it automatically multiply that cell by 25.4
(essentially
> > > converting a number from in. to mm.), keeping the new number in that
same
> > > cell:  enter 2 into A2 and it converts it to 50.8 and A2 now shows
50.8.
> > >
> > > Is this possible?
> > >
> > > Thanks.


0
bob.phillips1 (6510)
4/27/2005 9:15:45 PM
How does this code fit in with Peo's code?  Again, I really don't understand 
much VBA at all, so I definitely need the help.  Thanks so much!

-Mark

"Bob Phillips" wrote:

> Hi Mark,
> 
> Slight amendment then
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
>     On Error GoTo ws_exit:
>     Application.EnableEvents = False
>     With Target
>         If .Column = 1 Then
>             If Not .Value = "" Then
>                 .Value = .Value * 2.54
>             End If
>         End If
>     End With
> 
> ws_exit:
>     Application.EnableEvents = True
> End Sub
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "MAWII" <MAWII@discussions.microsoft.com> wrote in message
> news:B4997AED-664B-4E22-9EDE-4BC7FBD731C6@microsoft.com...
> > That's pretty cool.  I've got it working for the range of cells I need it
> to
> > work for.  Is there a way to keep the cell empty if there's nothing in it?
> > If you accidentally type something into a cell and delete it, the cell
> > returns a 0, and that will ultimately corrupt my future calculations.  I
> just
> > need empty cells to stay blank.  Thanks!
> >
> > -Mark
> >
> > "Peo Sjoblom" wrote:
> >
> > > Not by formatting, do you want this in any cell in the sheet or just one
> cell?
> > > Assume the latter and A2
> > >
> > > It would take an event macro so if someone else is using it they have to
> > > enable the macro when they open the workbook, if not it won't work
> > > Riight click the sheet tab where you want this, select view code and
> paste in
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > > If Intersect(Range("A2"), Target) Is Nothing Then Exit Sub
> > > Application.EnableEvents = False
> > > Target.Value = Target.Value * 25.4
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > > press alt + Q to close the VB editor
> > >
> > > now type an inch value in A2 and press enter
> > >
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "MAWII" wrote:
> > >
> > > > I'm trying to figure out a way (if there's one) to enter a number into
> a
> > > > blank cell and have it automatically multiply that cell by 25.4
> (essentially
> > > > converting a number from in. to mm.), keeping the new number in that
> same
> > > > cell:  enter 2 into A2 and it converts it to 50.8 and A2 now shows
> 50.8.
> > > >
> > > > Is this possible?
> > > >
> > > > Thanks.
> 
> 
> 
0
MAWII (9)
4/27/2005 10:32:03 PM
Not by formatting, do you want this in any cell in the sheet or just one cell?
Assume the latter and A2

It would take an event macro so if someone else is using it they have to 
enable the macro when they open the workbook, if not it won't work
Riight click the sheet tab where you want this, select view code and paste in

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A2"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = Target.Value * 25.4
Application.EnableEvents = True
End Sub

press alt + Q to close the VB editor

now type an inch value in A2 and press enter


Regards,

Peo Sjoblom











"MAWII" wrote:

> I'm trying to figure out a way (if there's one) to enter a number into a 
> blank cell and have it automatically multiply that cell by 25.4 (essentially 
> converting a number from in. to mm.), keeping the new number in that same 
> cell:  enter 2 into A2 and it converts it to 50.8 and A2 now shows 50.8.
> 
> Is this possible?
> 
> Thanks.
0
PeoSjoblom (789)
4/27/2005 10:59:31 PM
It doesn't. They are similar, choose one or the other and implement it.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"MAWII" <MAWII@discussions.microsoft.com> wrote in message
news:8A0BAFF5-1370-45F9-8E74-26DD0D7A3D5F@microsoft.com...
> How does this code fit in with Peo's code?  Again, I really don't
understand
> much VBA at all, so I definitely need the help.  Thanks so much!
>
> -Mark
>
> "Bob Phillips" wrote:
>
> > Hi Mark,
> >
> > Slight amendment then
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> >     On Error GoTo ws_exit:
> >     Application.EnableEvents = False
> >     With Target
> >         If .Column = 1 Then
> >             If Not .Value = "" Then
> >                 .Value = .Value * 2.54
> >             End If
> >         End If
> >     End With
> >
> > ws_exit:
> >     Application.EnableEvents = True
> > End Sub
> >
> > -- 
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "MAWII" <MAWII@discussions.microsoft.com> wrote in message
> > news:B4997AED-664B-4E22-9EDE-4BC7FBD731C6@microsoft.com...
> > > That's pretty cool.  I've got it working for the range of cells I need
it
> > to
> > > work for.  Is there a way to keep the cell empty if there's nothing in
it?
> > > If you accidentally type something into a cell and delete it, the cell
> > > returns a 0, and that will ultimately corrupt my future calculations.
I
> > just
> > > need empty cells to stay blank.  Thanks!
> > >
> > > -Mark
> > >
> > > "Peo Sjoblom" wrote:
> > >
> > > > Not by formatting, do you want this in any cell in the sheet or just
one
> > cell?
> > > > Assume the latter and A2
> > > >
> > > > It would take an event macro so if someone else is using it they
have to
> > > > enable the macro when they open the workbook, if not it won't work
> > > > Riight click the sheet tab where you want this, select view code and
> > paste in
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > > > If Intersect(Range("A2"), Target) Is Nothing Then Exit Sub
> > > > Application.EnableEvents = False
> > > > Target.Value = Target.Value * 25.4
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > > press alt + Q to close the VB editor
> > > >
> > > > now type an inch value in A2 and press enter
> > > >
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "MAWII" wrote:
> > > >
> > > > > I'm trying to figure out a way (if there's one) to enter a number
into
> > a
> > > > > blank cell and have it automatically multiply that cell by 25.4
> > (essentially
> > > > > converting a number from in. to mm.), keeping the new number in
that
> > same
> > > > > cell:  enter 2 into A2 and it converts it to 50.8 and A2 now shows
> > 50.8.
> > > > >
> > > > > Is this possible?
> > > > >
> > > > > Thanks.
> >
> >
> >


0
bob.phillips1 (6510)
4/28/2005 10:05:39 AM
I can't seem to get this one to work at all.  I've got it pasted into the 
correct sheet's module, but it's just not working.  Is there anyway to edit 
the code from Peo (which is working) to have it return a blank cell if what's 
there is deleted?

Thanks for your time and effort with this!

"Bob Phillips" wrote:

> Hi Mark,
> 
> Slight amendment then
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
>     On Error GoTo ws_exit:
>     Application.EnableEvents = False
>     With Target
>         If .Column = 1 Then
>             If Not .Value = "" Then
>                 .Value = .Value * 2.54
>             End If
>         End If
>     End With
> 
> ws_exit:
>     Application.EnableEvents = True
> End Sub
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "MAWII" <MAWII@discussions.microsoft.com> wrote in message
> news:B4997AED-664B-4E22-9EDE-4BC7FBD731C6@microsoft.com...
> > That's pretty cool.  I've got it working for the range of cells I need it
> to
> > work for.  Is there a way to keep the cell empty if there's nothing in it?
> > If you accidentally type something into a cell and delete it, the cell
> > returns a 0, and that will ultimately corrupt my future calculations.  I
> just
> > need empty cells to stay blank.  Thanks!
> >
> > -Mark
> >
> > "Peo Sjoblom" wrote:
> >
> > > Not by formatting, do you want this in any cell in the sheet or just one
> cell?
> > > Assume the latter and A2
> > >
> > > It would take an event macro so if someone else is using it they have to
> > > enable the macro when they open the workbook, if not it won't work
> > > Riight click the sheet tab where you want this, select view code and
> paste in
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > > If Intersect(Range("A2"), Target) Is Nothing Then Exit Sub
> > > Application.EnableEvents = False
> > > Target.Value = Target.Value * 25.4
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > > press alt + Q to close the VB editor
> > >
> > > now type an inch value in A2 and press enter
> > >
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "MAWII" wrote:
> > >
> > > > I'm trying to figure out a way (if there's one) to enter a number into
> a
> > > > blank cell and have it automatically multiply that cell by 25.4
> (essentially
> > > > converting a number from in. to mm.), keeping the new number in that
> same
> > > > cell:  enter 2 into A2 and it converts it to 50.8 and A2 now shows
> 50.8.
> > > >
> > > > Is this possible?
> > > >
> > > > Thanks.
> 
> 
> 
0
MAWII (9)
4/28/2005 4:32:18 PM
Nevermind--I just fixed my own problem by somehow figuring out how to put an 
if statement in to Peo's code similar to your .Value = " ".

Thanks for your help--sorry for any headaches I may have caused!

-Mark

"MAWII" wrote:

> I can't seem to get this one to work at all.  I've got it pasted into the 
> correct sheet's module, but it's just not working.  Is there anyway to edit 
> the code from Peo (which is working) to have it return a blank cell if what's 
> there is deleted?
> 
> Thanks for your time and effort with this!
> 
> "Bob Phillips" wrote:
> 
> > Hi Mark,
> > 
> > Slight amendment then
> > 
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > 
> >     On Error GoTo ws_exit:
> >     Application.EnableEvents = False
> >     With Target
> >         If .Column = 1 Then
> >             If Not .Value = "" Then
> >                 .Value = .Value * 2.54
> >             End If
> >         End If
> >     End With
> > 
> > ws_exit:
> >     Application.EnableEvents = True
> > End Sub
> > 
> > -- 
> > 
> > HTH
> > 
> > RP
> > (remove nothere from the email address if mailing direct)
> > 
> > 
> > "MAWII" <MAWII@discussions.microsoft.com> wrote in message
> > news:B4997AED-664B-4E22-9EDE-4BC7FBD731C6@microsoft.com...
> > > That's pretty cool.  I've got it working for the range of cells I need it
> > to
> > > work for.  Is there a way to keep the cell empty if there's nothing in it?
> > > If you accidentally type something into a cell and delete it, the cell
> > > returns a 0, and that will ultimately corrupt my future calculations.  I
> > just
> > > need empty cells to stay blank.  Thanks!
> > >
> > > -Mark
> > >
> > > "Peo Sjoblom" wrote:
> > >
> > > > Not by formatting, do you want this in any cell in the sheet or just one
> > cell?
> > > > Assume the latter and A2
> > > >
> > > > It would take an event macro so if someone else is using it they have to
> > > > enable the macro when they open the workbook, if not it won't work
> > > > Riight click the sheet tab where you want this, select view code and
> > paste in
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > > > If Intersect(Range("A2"), Target) Is Nothing Then Exit Sub
> > > > Application.EnableEvents = False
> > > > Target.Value = Target.Value * 25.4
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > > press alt + Q to close the VB editor
> > > >
> > > > now type an inch value in A2 and press enter
> > > >
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "MAWII" wrote:
> > > >
> > > > > I'm trying to figure out a way (if there's one) to enter a number into
> > a
> > > > > blank cell and have it automatically multiply that cell by 25.4
> > (essentially
> > > > > converting a number from in. to mm.), keeping the new number in that
> > same
> > > > > cell:  enter 2 into A2 and it converts it to 50.8 and A2 now shows
> > 50.8.
> > > > >
> > > > > Is this possible?
> > > > >
> > > > > Thanks.
> > 
> > 
> > 
0
MAWII (9)
4/28/2005 4:36:03 PM
Reply:

Similar Artilces: