Promt before changing a value

I was asked the below question by someone and was wondering if you
guys have any advise I can relay to him.

"I am looking to create something, most likely with VB code, in excel
that will pop up a dialog box to warn the user that they are changing
the quantity of a part by a more than normal amount.  For example, if
the current inventory quantity for a specific insert is 10,000, then
if that cell is changed by more than 3000, a dialog box will appear to
prompt the user to double check their change.  So, if that cell is
changed to 13,000 or higher, or, 7,000 or lower, the dialog box will
warn the user.  The code will be referencing not another cell, but
what the cell quantity was before the change compared to what the cell
quantity was changed to.

Any ideas?"
0
dwaldman20 (21)
9/17/2008 6:29:10 PM
excel 39879 articles. 2 followers. Follow

16 Replies
505 Views

Similar Articles

[PageSpeed] 19

You could do that with Data Validation

Set number minimum at 7001 and maximum at 12999


Gord Dibben  MS Excel MVP

On Wed, 17 Sep 2008 11:29:10 -0700 (PDT), Mangler <dwaldman20@gmail.com>
wrote:

>I was asked the below question by someone and was wondering if you
>guys have any advise I can relay to him.
>
>"I am looking to create something, most likely with VB code, in excel
>that will pop up a dialog box to warn the user that they are changing
>the quantity of a part by a more than normal amount.  For example, if
>the current inventory quantity for a specific insert is 10,000, then
>if that cell is changed by more than 3000, a dialog box will appear to
>prompt the user to double check their change.  So, if that cell is
>changed to 13,000 or higher, or, 7,000 or lower, the dialog box will
>warn the user.  The code will be referencing not another cell, but
>what the cell quantity was before the change compared to what the cell
>quantity was changed to.
>
>Any ideas?"

0
Gord
9/17/2008 6:42:57 PM
On Sep 17, 2:42=A0pm, Gord Dibben <gorddibbATshawDOTca> wrote:
> You could do that with Data Validation
>
> Set number minimum at 7001 and maximum at 12999
>
> Gord Dibben =A0MS Excel MVP
>
> On Wed, 17 Sep 2008 11:29:10 -0700 (PDT), Mangler <dwaldma...@gmail.com>
> wrote:
>
>
>
> >I was asked the below question by someone and was wondering if you
> >guys have any advise I can relay to him.
>
> >"I am looking to create something, most likely with VB code, in excel
> >that will pop up a dialog box to warn the user that they are changing
> >the quantity of a part by a more than normal amount. =A0For example, if
> >the current inventory quantity for a specific insert is 10,000, then
> >if that cell is changed by more than 3000, a dialog box will appear to
> >prompt the user to double check their change. =A0So, if that cell is
> >changed to 13,000 or higher, or, 7,000 or lower, the dialog box will
> >warn the user. =A0The code will be referencing not another cell, but
> >what the cell quantity was before the change compared to what the cell
> >quantity was changed to.
>
> >Any ideas?"- Hide quoted text -
>
> - Show quoted text -

I see how that works now thanks!  Next question on the same subject,
what if there is lots of data in a column and I wanted to warn the
user only when they are changing the value by 3000.  So if they made
an edit to a cell but the change was less then +-3000 they wont get
the message but if they changed the value to by more then 3000 they
would get the prompt.
0
dwaldman20 (21)
9/17/2008 7:03:59 PM
Right-click the sheet tab, enter this:

Public Amt

Private Sub Worksheet_Change(ByVal Target As Range)
    If IsNumeric(Target.Value) Then
        If Abs(Target.Value - Amt) / Amt > 0.3 Then MsgBox "Changed by " & 
Format(Abs(Target.Value - Amt) / Amt, "0.0%") & "  - was " & Amt
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Amt = Target.Value
    If Amt = 0 Then Amt = 1
End Sub


"Mangler" <dwaldman20@gmail.com> wrote in message 
news:b83e36f1-2ad3-42dd-a755-f92f4f103ff7@m45g2000hsb.googlegroups.com...
>I was asked the below question by someone and was wondering if you
> guys have any advise I can relay to him.
>
> "I am looking to create something, most likely with VB code, in excel
> that will pop up a dialog box to warn the user that they are changing
> the quantity of a part by a more than normal amount.  For example, if
> the current inventory quantity for a specific insert is 10,000, then
> if that cell is changed by more than 3000, a dialog box will appear to
> prompt the user to double check their change.  So, if that cell is
> changed to 13,000 or higher, or, 7,000 or lower, the dialog box will
> warn the user.  The code will be referencing not another cell, but
> what the cell quantity was before the change compared to what the cell
> quantity was changed to.
>
> Any ideas?" 


0
bobumlas (25)
9/17/2008 7:06:43 PM
On Sep 17, 3:06=A0pm, "Bob Umlas" <bobum...@yahoo.com> wrote:
> Right-click the sheet tab, enter this:
>
> Public Amt
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> =A0 =A0 If IsNumeric(Target.Value) Then
> =A0 =A0 =A0 =A0 If Abs(Target.Value - Amt) / Amt > 0.3 Then MsgBox "Chang=
ed by " &
> Format(Abs(Target.Value - Amt) / Amt, "0.0%") & " =A0- was " & Amt
> =A0 =A0 End If
> End Sub
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> =A0 =A0 Amt =3D Target.Value
> =A0 =A0 If Amt =3D 0 Then Amt =3D 1
> End Sub
>
> "Mangler" <dwaldma...@gmail.com> wrote in message
>
> news:b83e36f1-2ad3-42dd-a755-f92f4f103ff7@m45g2000hsb.googlegroups.com...
>
>
>
> >I was asked the below question by someone and was wondering if you
> > guys have any advise I can relay to him.
>
> > "I am looking to create something, most likely with VB code, in excel
> > that will pop up a dialog box to warn the user that they are changing
> > the quantity of a part by a more than normal amount. =A0For example, if
> > the current inventory quantity for a specific insert is 10,000, then
> > if that cell is changed by more than 3000, a dialog box will appear to
> > prompt the user to double check their change. =A0So, if that cell is
> > changed to 13,000 or higher, or, 7,000 or lower, the dialog box will
> > warn the user. =A0The code will be referencing not another cell, but
> > what the cell quantity was before the change compared to what the cell
> > quantity was changed to.
>
> > Any ideas?"- Hide quoted text -
>
> - Show quoted text -

Right clicked the sheet, view code and entered that but when i make a
change in a cell i am getting this error :

Compile error:
syntax error

and this line is highlighted in the debugger:

Private Sub Worksheet_Change(ByVal Target As Range)
0
dwaldman20 (21)
9/17/2008 7:13:57 PM
"Bob Umlas" <bobum...@yahoo.com> wrote...
....
>
>Public Amt
>
>Private Sub Worksheet_Change(ByVal Target As Range)
....

VBA variables aren't persistent. If anything in any module throws a
runtime error, all global and local static variables are reset. This
is a fragile approach. Then there's the pickier matter of declaring
Amt Public, so global scope, rather than Private, so module scope. And
also the runtime errors that would occur when users enter numeric
values into cells that had previously been nonnumeric; IOW, your code
checks the new value's type but not the previous value's type.

If the cell in question should revert to the previous value if the
user doesn't confirm entering a value outside the swing range, this
can be done without storing the previous value in a global variable.


Private Sub Worksheet_Change(ByVal Target As Range)
  Dim v As Variant, t As Variant, ac As Variant
  Dim i As Long, j As Long, k As Long, tac As Long

  On Error GoTo CleanUp
  Application.EnableCancelKey = xlDisabled
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  ac = Application.Calculation
  Application.Calculation = xlCalculationManual

  tac = Target.Areas.Count
  ReDim v(1 To tac)

  For k = 1 To tac
    v(k) = Target.Areas(k).Value2

    If Not IsArray(v(k)) Then
      ReDim t(1 To 1, 1 To 1)
      t(1, 1) = v(k)
      v(k) = t
      Erase t
    End If

  Next k

  Application.Undo

  For k = 1 To tac
    With Target.Areas(k)
      For i = 1 To .Rows.Count
        For j = 1 To .Columns.Count

          If VarType(.Cells(i, j).Value2) = vbDouble Then 'was numeric

            If VarType(v(k)(i, j)) = vbDouble Then 'still numeric

              If Abs(v(k)(i, j) / .Cells(i, j).Value2 - 1) >= 0.3 Then

                If MsgBox( _
                 Prompt:="Entered value " & v(k)(i, j) & _
                 " differs from previous value " & .Cells(i, j).Value2
& _
                 " by more than 30%." & vbLf & _
                 "Do you want to make this change?", _
                 Title:="cell " & .Cells(i, j).Address(0, 0, xlA1, 0),
_
                 Buttons:=vbYesNo _
                ) = vbYes Then .Cells(i, j).Value2 = v(k)(i, j)

              Else
                .Cells(i, j).Value2 = v(k)(i, j)

              End If

            Else 'no longer numeric

              If MsgBox( _
               Prompt:="Entered value " & v(k)(i, j) & _
               " is nonnumeric but previous value " & .Cells(i,
j).Value2 & _
               " was numeric." & vbLf & "Do you want to make this
change?", _
               Title:="cell " & .Cells(i, j).Address(0, 0, xlA1, 0), _
               Buttons:=vbYesNo _
              ) = vbYes Then .Cells(i, j).Value2 = v(k)(i, j)

            End If

          Else 'wasn't numeric, so any & all changes accepted
            .Cells(i, j).Value2 = v(k)(i, j)

          End If

        Next j
      Next i
    End With
  Next k

CleanUp:
  Application.Calculation = ac
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Application.EnableCancelKey = xlInterrupt

End Sub


Better still would be intersecting Target with a named range outside
which this check need not be made.
0
hrlngrv1 (375)
9/17/2008 8:09:55 PM
On Sep 17, 4:09=A0pm, Harlan Grove <hrln...@gmail.com> wrote:
> "Bob Umlas" <bobum...@yahoo.com> wrote...
>
> ...
>
> >Public Amt
>
> >Private Sub Worksheet_Change(ByVal Target As Range)
>
> ...
>
> VBA variables aren't persistent. If anything in any module throws a
> runtime error, all global and local static variables are reset. This
> is a fragile approach. Then there's the pickier matter of declaring
> Amt Public, so global scope, rather than Private, so module scope. And
> also the runtime errors that would occur when users enter numeric
> values into cells that had previously been nonnumeric; IOW, your code
> checks the new value's type but not the previous value's type.
>
> If the cell in question should revert to the previous value if the
> user doesn't confirm entering a value outside the swing range, this
> can be done without storing the previous value in a global variable.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> =A0 Dim v As Variant, t As Variant, ac As Variant
> =A0 Dim i As Long, j As Long, k As Long, tac As Long
>
> =A0 On Error GoTo CleanUp
> =A0 Application.EnableCancelKey =3D xlDisabled
> =A0 Application.EnableEvents =3D False
> =A0 Application.ScreenUpdating =3D False
> =A0 ac =3D Application.Calculation
> =A0 Application.Calculation =3D xlCalculationManual
>
> =A0 tac =3D Target.Areas.Count
> =A0 ReDim v(1 To tac)
>
> =A0 For k =3D 1 To tac
> =A0 =A0 v(k) =3D Target.Areas(k).Value2
>
> =A0 =A0 If Not IsArray(v(k)) Then
> =A0 =A0 =A0 ReDim t(1 To 1, 1 To 1)
> =A0 =A0 =A0 t(1, 1) =3D v(k)
> =A0 =A0 =A0 v(k) =3D t
> =A0 =A0 =A0 Erase t
> =A0 =A0 End If
>
> =A0 Next k
>
> =A0 Application.Undo
>
> =A0 For k =3D 1 To tac
> =A0 =A0 With Target.Areas(k)
> =A0 =A0 =A0 For i =3D 1 To .Rows.Count
> =A0 =A0 =A0 =A0 For j =3D 1 To .Columns.Count
>
> =A0 =A0 =A0 =A0 =A0 If VarType(.Cells(i, j).Value2) =3D vbDouble Then 'wa=
s numeric
>
> =A0 =A0 =A0 =A0 =A0 =A0 If VarType(v(k)(i, j)) =3D vbDouble Then 'still n=
umeric
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 If Abs(v(k)(i, j) / .Cells(i, j).Value2 - 1) =
>=3D 0.3 Then
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 If MsgBox( _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Prompt:=3D"Entered value " & v(k)(i, j=
) & _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" differs from previous value " & .Cel=
ls(i, j).Value2
> & _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" by more than 30%." & vbLf & _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0"Do you want to make this change?", _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Title:=3D"cell " & .Cells(i, j).Addres=
s(0, 0, xlA1, 0),
> _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Buttons:=3DvbYesNo _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ) =3D vbYes Then .Cells(i, j).Value2 =3D =
v(k)(i, j)
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 Else
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 .Cells(i, j).Value2 =3D v(k)(i, j)
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 End If
>
> =A0 =A0 =A0 =A0 =A0 =A0 Else 'no longer numeric
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 If MsgBox( _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Prompt:=3D"Entered value " & v(k)(i, j) & =
_
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" is nonnumeric but previous value " & .Ce=
lls(i,
> j).Value2 & _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" was numeric." & vbLf & "Do you want to m=
ake this
> change?", _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Title:=3D"cell " & .Cells(i, j).Address(0,=
 0, xlA1, 0), _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Buttons:=3DvbYesNo _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 ) =3D vbYes Then .Cells(i, j).Value2 =3D v(k)=
(i, j)
>
> =A0 =A0 =A0 =A0 =A0 =A0 End If
>
> =A0 =A0 =A0 =A0 =A0 Else 'wasn't numeric, so any & all changes accepted
> =A0 =A0 =A0 =A0 =A0 =A0 .Cells(i, j).Value2 =3D v(k)(i, j)
>
> =A0 =A0 =A0 =A0 =A0 End If
>
> =A0 =A0 =A0 =A0 Next j
> =A0 =A0 =A0 Next i
> =A0 =A0 End With
> =A0 Next k
>
> CleanUp:
> =A0 Application.Calculation =3D ac
> =A0 Application.ScreenUpdating =3D True
> =A0 Application.EnableEvents =3D True
> =A0 Application.EnableCancelKey =3D xlInterrupt
>
> End Sub
>
> Better still would be intersecting Target with a named range outside
> which this check need not be made.

Thanks for the code!  I am getting a syntax error in these 2 areas :

If MsgBox( _
                 Prompt:=3D"Entered value " & v(k)(i, j) & _
                 " differs from previous value " & .Cells(i, j).Value2
& _
                 " by more than 30%." & vbLf & _
                 "Do you want to make this change?", _
                 Title:=3D"cell " & .Cells(i, j).Address(0, 0, xlA1, 0),
_
                 Buttons:=3DvbYesNo _
                ) =3D vbYes Then .Cells(i, j).Value2 =3D v(k)(i, j)



If MsgBox( _
               Prompt:=3D"Entered value " & v(k)(i, j) & _
               " is nonnumeric but previous value " & .Cells(i,
j).Value2 & _
               " was numeric." & vbLf & "Do you want to make this
change?", _
               Title:=3D"cell " & .Cells(i, j).Address(0, 0, xlA1, 0), _
               Buttons:=3DvbYesNo _
              ) =3D vbYes Then .Cells(i, j).Value2 =3D v(k)(i, j)


I dont know enough about VB to correct the syntax, can you help me out
real quick?
0
dwaldman20 (21)
9/17/2008 8:19:23 PM
On Sep 17, 1:19=A0pm, Mangler <dwaldma...@gmail.com> wrote:
....
>Thanks for the code! =A0I am getting a syntax error in these 2 areas :
>
>If MsgBox( _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Prompt:=3D"Entered value " & v(k)(i, j=
) & _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" differs from previous value " & .Cel=
ls(i, j).Value2
> & _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" by more than 30%." & vbLf & _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0"Do you want to make this change?", _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Title:=3D"cell " & .Cells(i, j).Addres=
s(0, 0, xlA1, 0),
> _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Buttons:=3DvbYesNo _
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ) =3D vbYes Then .Cells(i, j).Value2 =3D =
v(k)(i, j)
....

Probably because the code is line wrapping. Any line of code other
than the initial Sub line and the final End Sub line should be
indented in from the left. If any other lines show nonblank characters
at the beginning of the line, they should be at the end of the
preceding line.

>I dont know enough about VB to correct the syntax, can you help me out
>real quick?

Here's the code without any indentation.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim v As Variant, t As Variant, ac As Variant
Dim i As Long, j As Long, k As Long, tac As Long

On Error GoTo CleanUp
Application.EnableCancelKey =3D xlDisabled
Application.EnableEvents =3D False
Application.ScreenUpdating =3D False
ac =3D Application.Calculation
Application.Calculation =3D xlCalculationManual

tac =3D Target.Areas.Count
ReDim v(1 To tac)

For k =3D 1 To tac
v(k) =3D Target.Areas(k).Value2

If Not IsArray(v(k)) Then
ReDim t(1 To 1, 1 To 1)
t(1, 1) =3D v(k)
v(k) =3D t
Erase t
End If

Next k

Application.Undo

For k =3D 1 To tac
With Target.Areas(k)
For i =3D 1 To .Rows.Count
For j =3D 1 To .Columns.Count

If VarType(.Cells(i, j).Value2) =3D vbDouble Then 'was numeric

If VarType(v(k)(i, j)) =3D vbDouble Then 'still numeric

If Abs(v(k)(i, j) / .Cells(i, j).Value2 - 1) >=3D 0.3 Then

If MsgBox( _
Prompt:=3D"Entered value " & v(k)(i, j) & _
" differs from previous value " & .Cells(i, j).Value2 & _
" by more than 30%." & vbLf & _
"Do you want to make this change?", _
Title:=3D"cell " & .Cells(i, j).Address(0, 0, xlA1, 0), _
Buttons:=3DvbYesNo _
) =3D vbYes Then .Cells(i, j).Value2 =3D v(k)(i, j)

Else
..Cells(i, j).Value2 =3D v(k)(i, j)

End If

Else 'no longer numeric

If MsgBox( _
Prompt:=3D"Entered value " & v(k)(i, j) & _
" is nonnumeric but previous value " & .Cells(i, j).Value2 & _
" was numeric." & vbLf & "Do you want to make this change?", _
Title:=3D"cell " & .Cells(i, j).Address(0, 0, xlA1, 0), _
Buttons:=3DvbYesNo _
) =3D vbYes Then .Cells(i, j).Value2 =3D v(k)(i, j)

End If

Else 'wasn't numeric, so any & all changes accepted
..Cells(i, j).Value2 =3D v(k)(i, j)

End If

Next j
Next i
End With
Next k

CleanUp:
Application.Calculation =3D ac
Application.ScreenUpdating =3D True
Application.EnableEvents =3D True
Application.EnableCancelKey =3D xlInterrupt

End Sub
0
hrlngrv1 (375)
9/17/2008 8:30:01 PM
On Sep 17, 4:30=A0pm, Harlan Grove <hrln...@gmail.com> wrote:
> On Sep 17, 1:19=A0pm, Mangler <dwaldma...@gmail.com> wrote:
> ...>Thanks for the code! =A0I am getting a syntax error in these 2 areas =
:
>
> >If MsgBox( _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Prompt:=3D"Entered value " & v(k)(i,=
 j) & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" differs from previous value " & .C=
ells(i, j).Value2
> > & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" by more than 30%." & vbLf & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0"Do you want to make this change?", =
_
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Title:=3D"cell " & .Cells(i, j).Addr=
ess(0, 0, xlA1, 0),
> > _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Buttons:=3DvbYesNo _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ) =3D vbYes Then .Cells(i, j).Value2 =
=3D v(k)(i, j)
>
> ...
>
> Probably because the code is line wrapping. Any line of code other
> than the initial Sub line and the final End Sub line should be
> indented in from the left. If any other lines show nonblank characters
> at the beginning of the line, they should be at the end of the
> preceding line.
>
> >I dont know enough about VB to correct the syntax, can you help me out
> >real quick?
>
> Here's the code without any indentation.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim v As Variant, t As Variant, ac As Variant
> Dim i As Long, j As Long, k As Long, tac As Long
>
> On Error GoTo CleanUp
> Application.EnableCancelKey =3D xlDisabled
> Application.EnableEvents =3D False
> Application.ScreenUpdating =3D False
> ac =3D Application.Calculation
> Application.Calculation =3D xlCalculationManual
>
> tac =3D Target.Areas.Count
> ReDim v(1 To tac)
>
> For k =3D 1 To tac
> v(k) =3D Target.Areas(k).Value2
>
> If Not IsArray(v(k)) Then
> ReDim t(1 To 1, 1 To 1)
> t(1, 1) =3D v(k)
> v(k) =3D t
> Erase t
> End If
>
> Next k
>
> Application.Undo
>
> For k =3D 1 To tac
> With Target.Areas(k)
> For i =3D 1 To .Rows.Count
> For j =3D 1 To .Columns.Count
>
> If VarType(.Cells(i, j).Value2) =3D vbDouble Then 'was numeric
>
> If VarType(v(k)(i, j)) =3D vbDouble Then 'still numeric
>
> If Abs(v(k)(i, j) / .Cells(i, j).Value2 - 1) >=3D 0.3 Then
>
> If MsgBox( _
> Prompt:=3D"Entered value " & v(k)(i, j) & _
> " differs from previous value " & .Cells(i, j).Value2 & _
> " by more than 30%." & vbLf & _
> "Do you want to make this change?", _
> Title:=3D"cell " & .Cells(i, j).Address(0, 0, xlA1, 0), _
> Buttons:=3DvbYesNo _
> ) =3D vbYes Then .Cells(i, j).Value2 =3D v(k)(i, j)
>
> Else
> .Cells(i, j).Value2 =3D v(k)(i, j)
>
> End If
>
> Else 'no longer numeric
>
> If MsgBox( _
> Prompt:=3D"Entered value " & v(k)(i, j) & _
> " is nonnumeric but previous value " & .Cells(i, j).Value2 & _
> " was numeric." & vbLf & "Do you want to make this change?", _
> Title:=3D"cell " & .Cells(i, j).Address(0, 0, xlA1, 0), _
> Buttons:=3DvbYesNo _
> ) =3D vbYes Then .Cells(i, j).Value2 =3D v(k)(i, j)
>
> End If
>
> Else 'wasn't numeric, so any & all changes accepted
> .Cells(i, j).Value2 =3D v(k)(i, j)
>
> End If
>
> Next j
> Next i
> End With
> Next k
>
> CleanUp:
> Application.Calculation =3D ac
> Application.ScreenUpdating =3D True
> Application.EnableEvents =3D True
> Application.EnableCancelKey =3D xlInterrupt
>
> End Sub

Thank you sir!  You save me a headache.
0
dwaldman20 (21)
9/17/2008 8:41:10 PM
On Sep 17, 4:30=A0pm, Harlan Grove <hrln...@gmail.com> wrote:
> On Sep 17, 1:19=A0pm, Mangler <dwaldma...@gmail.com> wrote:
> ...>Thanks for the code! =A0I am getting a syntax error in these 2 areas =
:
>
> >If MsgBox( _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Prompt:=3D"Entered value " & v(k)(i,=
 j) & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" differs from previous value " & .C=
ells(i, j).Value2
> > & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" by more than 30%." & vbLf & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0"Do you want to make this change?", =
_
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Title:=3D"cell " & .Cells(i, j).Addr=
ess(0, 0, xlA1, 0),
> > _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Buttons:=3DvbYesNo _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ) =3D vbYes Then .Cells(i, j).Value2 =
=3D v(k)(i, j)
>
> ...
>
> Probably because the code is line wrapping. Any line of code other
> than the initial Sub line and the final End Sub line should be
> indented in from the left. If any other lines show nonblank characters
> at the beginning of the line, they should be at the end of the
> preceding line.
>
> >I dont know enough about VB to correct the syntax, can you help me out
> >real quick?
>
> Here's the code without any indentation.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim v As Variant, t As Variant, ac As Variant
> Dim i As Long, j As Long, k As Long, tac As Long
>
> On Error GoTo CleanUp
> Application.EnableCancelKey =3D xlDisabled
> Application.EnableEvents =3D False
> Application.ScreenUpdating =3D False
> ac =3D Application.Calculation
> Application.Calculation =3D xlCalculationManual
>
> tac =3D Target.Areas.Count
> ReDim v(1 To tac)
>
> For k =3D 1 To tac
> v(k) =3D Target.Areas(k).Value2
>
> If Not IsArray(v(k)) Then
> ReDim t(1 To 1, 1 To 1)
> t(1, 1) =3D v(k)
> v(k) =3D t
> Erase t
> End If
>
> Next k
>
> Application.Undo
>
> For k =3D 1 To tac
> With Target.Areas(k)
> For i =3D 1 To .Rows.Count
> For j =3D 1 To .Columns.Count
>
> If VarType(.Cells(i, j).Value2) =3D vbDouble Then 'was numeric
>
> If VarType(v(k)(i, j)) =3D vbDouble Then 'still numeric
>
> If Abs(v(k)(i, j) / .Cells(i, j).Value2 - 1) >=3D 0.3 Then
>
> If MsgBox( _
> Prompt:=3D"Entered value " & v(k)(i, j) & _
> " differs from previous value " & .Cells(i, j).Value2 & _
> " by more than 30%." & vbLf & _
> "Do you want to make this change?", _
> Title:=3D"cell " & .Cells(i, j).Address(0, 0, xlA1, 0), _
> Buttons:=3DvbYesNo _
> ) =3D vbYes Then .Cells(i, j).Value2 =3D v(k)(i, j)
>
> Else
> .Cells(i, j).Value2 =3D v(k)(i, j)
>
> End If
>
> Else 'no longer numeric
>
> If MsgBox( _
> Prompt:=3D"Entered value " & v(k)(i, j) & _
> " is nonnumeric but previous value " & .Cells(i, j).Value2 & _
> " was numeric." & vbLf & "Do you want to make this change?", _
> Title:=3D"cell " & .Cells(i, j).Address(0, 0, xlA1, 0), _
> Buttons:=3DvbYesNo _
> ) =3D vbYes Then .Cells(i, j).Value2 =3D v(k)(i, j)
>
> End If
>
> Else 'wasn't numeric, so any & all changes accepted
> .Cells(i, j).Value2 =3D v(k)(i, j)
>
> End If
>
> Next j
> Next i
> End With
> Next k
>
> CleanUp:
> Application.Calculation =3D ac
> Application.ScreenUpdating =3D True
> Application.EnableEvents =3D True
> Application.EnableCancelKey =3D xlInterrupt
>
> End Sub

one quick thing, everything works fine except when the starting value
is 0.  If that is the case and a change is made the change
automatically gets reverted back to 0.  Is there a way around that?
0
dwaldman20 (21)
9/17/2008 8:58:09 PM
On Sep 17, 4:30=A0pm, Harlan Grove <hrln...@gmail.com> wrote:
> On Sep 17, 1:19=A0pm, Mangler <dwaldma...@gmail.com> wrote:
> ...>Thanks for the code! =A0I am getting a syntax error in these 2 areas =
:
>
> >If MsgBox( _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Prompt:=3D"Entered value " & v(k)(i,=
 j) & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" differs from previous value " & .C=
ells(i, j).Value2
> > & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0" by more than 30%." & vbLf & _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0"Do you want to make this change?", =
_
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Title:=3D"cell " & .Cells(i, j).Addr=
ess(0, 0, xlA1, 0),
> > _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Buttons:=3DvbYesNo _
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ) =3D vbYes Then .Cells(i, j).Value2 =
=3D v(k)(i, j)
>
> ...
>
> Probably because the code is line wrapping. Any line of code other
> than the initial Sub line and the final End Sub line should be
> indented in from the left. If any other lines show nonblank characters
> at the beginning of the line, they should be at the end of the
> preceding line.
>
> >I dont know enough about VB to correct the syntax, can you help me out
> >real quick?
>
> Here's the code without any indentation.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim v As Variant, t As Variant, ac As Variant
> Dim i As Long, j As Long, k As Long, tac As Long
>
> On Error GoTo CleanUp
> Application.EnableCancelKey =3D xlDisabled
> Application.EnableEvents =3D False
> Application.ScreenUpdating =3D False
> ac =3D Application.Calculation
> Application.Calculation =3D xlCalculationManual
>
> tac =3D Target.Areas.Count
> ReDim v(1 To tac)
>
> For k =3D 1 To tac
> v(k) =3D Target.Areas(k).Value2
>
> If Not IsArray(v(k)) Then
> ReDim t(1 To 1, 1 To 1)
> t(1, 1) =3D v(k)
> v(k) =3D t
> Erase t
> End If
>
> Next k
>
> Application.Undo
>
> For k =3D 1 To tac
> With Target.Areas(k)
> For i =3D 1 To .Rows.Count
> For j =3D 1 To .Columns.Count
>
> If VarType(.Cells(i, j).Value2) =3D vbDouble Then 'was numeric
>
> If VarType(v(k)(i, j)) =3D vbDouble Then 'still numeric
>
> If Abs(v(k)(i, j) / .Cells(i, j).Value2 - 1) >=3D 0.3 Then
>
> If MsgBox( _
> Prompt:=3D"Entered value " & v(k)(i, j) & _
> " differs from previous value " & .Cells(i, j).Value2 & _
> " by more than 30%." & vbLf & _
> "Do you want to make this change?", _
> Title:=3D"cell " & .Cells(i, j).Address(0, 0, xlA1, 0), _
> Buttons:=3DvbYesNo _
> ) =3D vbYes Then .Cells(i, j).Value2 =3D v(k)(i, j)
>
> Else
> .Cells(i, j).Value2 =3D v(k)(i, j)
>
> End If
>
> Else 'no longer numeric
>
> If MsgBox( _
> Prompt:=3D"Entered value " & v(k)(i, j) & _
> " is nonnumeric but previous value " & .Cells(i, j).Value2 & _
> " was numeric." & vbLf & "Do you want to make this change?", _
> Title:=3D"cell " & .Cells(i, j).Address(0, 0, xlA1, 0), _
> Buttons:=3DvbYesNo _
> ) =3D vbYes Then .Cells(i, j).Value2 =3D v(k)(i, j)
>
> End If
>
> Else 'wasn't numeric, so any & all changes accepted
> .Cells(i, j).Value2 =3D v(k)(i, j)
>
> End If
>
> Next j
> Next i
> End With
> Next k
>
> CleanUp:
> Application.Calculation =3D ac
> Application.ScreenUpdating =3D True
> Application.EnableEvents =3D True
> Application.EnableCancelKey =3D xlInterrupt
>
> End Sub

Ignore that last post :)

I do have one small problem though, I am unable to use the "undo" when
the code is on the sheet.  Is there a way I can use the code and still
have the undo function available at the same time?
0
dwaldman20 (21)
9/18/2008 2:12:36 PM
Mangler <dwaldma...@gmail.com> wrote...
....
>I do have one small problem though, I am unable to use the "undo" when
>the code is on the sheet. =A0Is there a way I can use the code and still
>have the undo function available at the same time?

Not without storing pre-existing values.

0
hrlngrv1 (375)
9/18/2008 3:43:30 PM
On Sep 18, 11:43=A0am, Harlan Grove <hrln...@gmail.com> wrote:
> Mangler <dwaldma...@gmail.com> wrote...
>
> ...
>
> >I do have one small problem though, I am unable to use the "undo" when
> >the code is on the sheet. =A0Is there a way I can use the code and still
> >have the undo function available at the same time?
>
> Not without storing pre-existing values.

And I assume thats not an quick and easy thing to accomplish?

( Thanks very much for your help on this by the way, I really
appreciate it )
0
dwaldman20 (21)
9/18/2008 4:00:54 PM
Mangler <dwaldma...@gmail.com> wrote...
>On Sep 18, 11:43=A0am, Harlan Grove <hrln...@gmail.com> wrote:
>>Mangler <dwaldma...@gmail.com> wrote...
>>>...
>>>I do have one small problem though, I am unable to use the "undo" when
>>>the code is on the sheet. =A0Is there a way I can use the code and still
>>>have the undo function available at the same time?
>>
>>Not without storing pre-existing values.
>
>And I assume thats not an quick and easy thing to accomplish?

The problem is that any time any macro, including event handlers,
changes any cell's contents, Excel purges the ENTIRE undo stack. So if
any macro changes the contents of any cell, you can't undo any changes
you made prior to running the macro. If you want a macro that could
reset cells to their previous contents whenever a user makes a change
that's outside the acceptable percentage change range, then if that
macro does reset a cell's contents, you can't undo any previous
changes.

If all you want is a warning that changes outside the acceptable range
have been made, then you'd need to adapt Bob Umlas's approach.
However, his approach isn't reliable if you or your users make entries
in multiple cell selections. If you're willing to use the
SelectionChange event handler to prevent multiple cell selections,
then you could change his approach to the following.


Option Explicit


Private v As Variant


Private Sub Worksheet_Change(ByVal Target As Range)
  Dim aeck As Variant

  On Error GoTo CleanUp
  aeck =3D Application.EnableCancelKey
  Application.EnableCancelKey =3D xlDisabled
  Application.EnableEvents =3D False

  If VarType(v) =3D vbDouble Then
    If VarType(Target.Value2) =3D vbDouble Then
      If v =3D 0 And Target.Value2 <> 0 Then
        MsgBox Buttons:=3DvbOKOnly, _
         Prompt:=3D"Cell value was zero but now is nonzero.", _
         Title:=3D"cell " & Target.Address(0, 0, xlA1, 0)
      ElseIf v <> 0 Then
        If Abs(Target.Value2 / v - 1) > 0.3 Then _
          MsgBox Buttons:=3DvbOKOnly, _
           Prompt:=3D"New cell value " & Target.Value2 & " has changed "
& _
           "by more than 30% from old cell value " & v, _
           Title:=3D"cell " & Target.Address(0, 0, xlA1, 0)
      End If
    Else
      MsgBox _
       Prompt:=3D"Cell value was numeric but now isn't numeric.", _
       Title:=3D"cell " & Target.Address(0, 0, xlA1, 0), _
       Buttons:=3DvbOKOnly
    End If
  End If

CleanUp:
  Application.EnableEvents =3D True
  Application.EnableCancelKey =3D aeck

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim aeck As Variant
  On Error GoTo CleanUp
  aeck =3D Application.EnableCancelKey
  Application.EnableCancelKey =3D xlDisabled
  Application.EnableEvents =3D False

  If Target.Cells.Count > 1 Then ActiveCell.Select
  v =3D ActiveCell.Value2

CleanUp:
  Application.EnableEvents =3D True
  Application.EnableCancelKey =3D aeck

End Sub
0
hrlngrv1 (375)
9/18/2008 6:34:02 PM
On Sep 18, 2:34=A0pm, Harlan Grove <hrln...@gmail.com> wrote:
> Mangler <dwaldma...@gmail.com> wrote...
> >On Sep 18, 11:43=A0am, Harlan Grove <hrln...@gmail.com> wrote:
> >>Mangler <dwaldma...@gmail.com> wrote...
> >>>...
> >>>I do have one small problem though, I am unable to use the "undo" when
> >>>the code is on the sheet. =A0Is there a way I can use the code and sti=
ll
> >>>have the undo function available at the same time?
>
> >>Not without storing pre-existing values.
>
> >And I assume thats not an quick and easy thing to accomplish?
>
> The problem is that any time any macro, including event handlers,
> changes any cell's contents, Excel purges the ENTIRE undo stack. So if
> any macro changes the contents of any cell, you can't undo any changes
> you made prior to running the macro. If you want a macro that could
> reset cells to their previous contents whenever a user makes a change
> that's outside the acceptable percentage change range, then if that
> macro does reset a cell's contents, you can't undo any previous
> changes.
>
> If all you want is a warning that changes outside the acceptable range
> have been made, then you'd need to adapt Bob Umlas's approach.
> However, his approach isn't reliable if you or your users make entries
> in multiple cell selections. If you're willing to use the
> SelectionChange event handler to prevent multiple cell selections,
> then you could change his approach to the following.
>
> Option Explicit
>
> Private v As Variant
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> =A0 Dim aeck As Variant
>
> =A0 On Error GoTo CleanUp
> =A0 aeck =3D Application.EnableCancelKey
> =A0 Application.EnableCancelKey =3D xlDisabled
> =A0 Application.EnableEvents =3D False
>
> =A0 If VarType(v) =3D vbDouble Then
> =A0 =A0 If VarType(Target.Value2) =3D vbDouble Then
> =A0 =A0 =A0 If v =3D 0 And Target.Value2 <> 0 Then
> =A0 =A0 =A0 =A0 MsgBox Buttons:=3DvbOKOnly, _
> =A0 =A0 =A0 =A0 =A0Prompt:=3D"Cell value was zero but now is nonzero.", _
> =A0 =A0 =A0 =A0 =A0Title:=3D"cell " & Target.Address(0, 0, xlA1, 0)
> =A0 =A0 =A0 ElseIf v <> 0 Then
> =A0 =A0 =A0 =A0 If Abs(Target.Value2 / v - 1) > 0.3 Then _
> =A0 =A0 =A0 =A0 =A0 MsgBox Buttons:=3DvbOKOnly, _
> =A0 =A0 =A0 =A0 =A0 =A0Prompt:=3D"New cell value " & Target.Value2 & " ha=
s changed "
> & _
> =A0 =A0 =A0 =A0 =A0 =A0"by more than 30% from old cell value " & v, _
> =A0 =A0 =A0 =A0 =A0 =A0Title:=3D"cell " & Target.Address(0, 0, xlA1, 0)
> =A0 =A0 =A0 End If
> =A0 =A0 Else
> =A0 =A0 =A0 MsgBox _
> =A0 =A0 =A0 =A0Prompt:=3D"Cell value was numeric but now isn't numeric.",=
 _
> =A0 =A0 =A0 =A0Title:=3D"cell " & Target.Address(0, 0, xlA1, 0), _
> =A0 =A0 =A0 =A0Buttons:=3DvbOKOnly
> =A0 =A0 End If
> =A0 End If
>
> CleanUp:
> =A0 Application.EnableEvents =3D True
> =A0 Application.EnableCancelKey =3D aeck
>
> End Sub
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> =A0 Dim aeck As Variant
> =A0 On Error GoTo CleanUp
> =A0 aeck =3D Application.EnableCancelKey
> =A0 Application.EnableCancelKey =3D xlDisabled
> =A0 Application.EnableEvents =3D False
>
> =A0 If Target.Cells.Count > 1 Then ActiveCell.Select
> =A0 v =3D ActiveCell.Value2
>
> CleanUp:
> =A0 Application.EnableEvents =3D True
> =A0 Application.EnableCancelKey =3D aeck
>
> End Sub

That is exactly what I needed!  Thanks again.
0
dwaldman20 (21)
9/19/2008 4:53:55 PM
On Sep 18, 2:34=A0pm, Harlan Grove <hrln...@gmail.com> wrote:
> Mangler <dwaldma...@gmail.com> wrote...
> >On Sep 18, 11:43=A0am, Harlan Grove <hrln...@gmail.com> wrote:
> >>Mangler <dwaldma...@gmail.com> wrote...
> >>>...
> >>>I do have one small problem though, I am unable to use the "undo" when
> >>>the code is on the sheet. =A0Is there a way I can use the code and sti=
ll
> >>>have the undo function available at the same time?
>
> >>Not without storing pre-existing values.
>
> >And I assume thats not an quick and easy thing to accomplish?
>
> The problem is that any time any macro, including event handlers,
> changes any cell's contents, Excel purges the ENTIRE undo stack. So if
> any macro changes the contents of any cell, you can't undo any changes
> you made prior to running the macro. If you want a macro that could
> reset cells to their previous contents whenever a user makes a change
> that's outside the acceptable percentage change range, then if that
> macro does reset a cell's contents, you can't undo any previous
> changes.
>
> If all you want is a warning that changes outside the acceptable range
> have been made, then you'd need to adapt Bob Umlas's approach.
> However, his approach isn't reliable if you or your users make entries
> in multiple cell selections. If you're willing to use the
> SelectionChange event handler to prevent multiple cell selections,
> then you could change his approach to the following.
>
> Option Explicit
>
> Private v As Variant
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> =A0 Dim aeck As Variant
>
> =A0 On Error GoTo CleanUp
> =A0 aeck =3D Application.EnableCancelKey
> =A0 Application.EnableCancelKey =3D xlDisabled
> =A0 Application.EnableEvents =3D False
>
> =A0 If VarType(v) =3D vbDouble Then
> =A0 =A0 If VarType(Target.Value2) =3D vbDouble Then
> =A0 =A0 =A0 If v =3D 0 And Target.Value2 <> 0 Then
> =A0 =A0 =A0 =A0 MsgBox Buttons:=3DvbOKOnly, _
> =A0 =A0 =A0 =A0 =A0Prompt:=3D"Cell value was zero but now is nonzero.", _
> =A0 =A0 =A0 =A0 =A0Title:=3D"cell " & Target.Address(0, 0, xlA1, 0)
> =A0 =A0 =A0 ElseIf v <> 0 Then
> =A0 =A0 =A0 =A0 If Abs(Target.Value2 / v - 1) > 0.3 Then _
> =A0 =A0 =A0 =A0 =A0 MsgBox Buttons:=3DvbOKOnly, _
> =A0 =A0 =A0 =A0 =A0 =A0Prompt:=3D"New cell value " & Target.Value2 & " ha=
s changed "
> & _
> =A0 =A0 =A0 =A0 =A0 =A0"by more than 30% from old cell value " & v, _
> =A0 =A0 =A0 =A0 =A0 =A0Title:=3D"cell " & Target.Address(0, 0, xlA1, 0)
> =A0 =A0 =A0 End If
> =A0 =A0 Else
> =A0 =A0 =A0 MsgBox _
> =A0 =A0 =A0 =A0Prompt:=3D"Cell value was numeric but now isn't numeric.",=
 _
> =A0 =A0 =A0 =A0Title:=3D"cell " & Target.Address(0, 0, xlA1, 0), _
> =A0 =A0 =A0 =A0Buttons:=3DvbOKOnly
> =A0 =A0 End If
> =A0 End If
>
> CleanUp:
> =A0 Application.EnableEvents =3D True
> =A0 Application.EnableCancelKey =3D aeck
>
> End Sub
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> =A0 Dim aeck As Variant
> =A0 On Error GoTo CleanUp
> =A0 aeck =3D Application.EnableCancelKey
> =A0 Application.EnableCancelKey =3D xlDisabled
> =A0 Application.EnableEvents =3D False
>
> =A0 If Target.Cells.Count > 1 Then ActiveCell.Select
> =A0 v =3D ActiveCell.Value2
>
> CleanUp:
> =A0 Application.EnableEvents =3D True
> =A0 Application.EnableCancelKey =3D aeck
>
> End Sub

Actually if your still out there I have one more question.  Seeing
that it obviously looks like your a pro with vb maybe you could refer
me to a good trusted source to learn a few things?
0
dwaldman20 (21)
9/22/2008 7:11:14 PM
Mangler <dwaldma...@gmail.com> wrote...
....
> . . . maybe you could refer me to a good trusted source to learn a few things?

Not me. I'm self-taught in VBA. I learned programming a long time ago
using other languages. I'm not familiar with what's available in
introductory VBA books.

You could try a Google Groups search in the Excel newsgroups for VBA
books and other learning resources. That's been asked and answered
many times.

0
hrlngrv1 (375)
9/22/2008 7:52:02 PM
Reply:

Similar Artilces:

Change Status of Closed Batches
After several years of using RMS without QuickBooks Integration we have now got it working. However, all of the previous batches are still showing. Is there a SQL Statement that will change the Status of these batches to Posted, so they will no longer appear and clutter up the "Post Closed Batches" window? We have 4 lanes, using Version 1.2. Thank You David, What I did when I got integrated was to use Quickbooks file for accounting type and posted all the old batches that way. After your done posting you can change the accounting type back to Quickbooks 2003 and your good to...

change business portal domain
When changing the Business Portal Domain, users are requesting a more automated process to update Portal Users information in the following tables for example:- MbfUserWindowsIdentity MbfUser Many sites have 100's of BP users and it is not fesible to remove and re-add user information based on the quantity of users. Scripting or a Utility to achieve the change is a viable option and would save Partners & Customer an immense amount of work, time and frustration. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the m...

Changing the WHITE background color of Money
Hi, I've just installed "Money financial suite 14.0" on my WinXP Pro. Does anyone know how to change the WHITE background color to another color (gray for instance) ? White kills your eyes + the pixels of your screen. White as a background color also goes against basic ergonomics principles ! Thanks! Christian "nospam" <nospam@nospam.com> wrote in message news:%23OmHLJF2EHA.2708@TK2MSFTNGP10.phx.gbl... > Hi, > > I've just installed "Money financial suite 14.0" on my WinXP Pro. > > Does anyone know how to change the WHITE ba...

Changing cell values through form
Hi... I'm not that good with access so I decided to look for help here. I have a table filled with drivers. Starting number, Driver name, and Number of laps are the columns. My Idea is to make a form that will list all the drivers, and add a value of +1 into the "Number of laps" cell to that driver when double clicked. That would be a sort of Lap counter form... Every suggestion is welcome. -- ::: www.ktvj-online.com - Motocros Portal ::: ::: www.forum.ktvj-online.com - Forum ::: ...

Re : Excel Range of Values Amidst Characteristic Transitions
Re : Excel Range of Values Amidst Characteristic Transitions Enter an Excel worksheet ; now that the tabulation is prepared for (preferential) presentation, Column A is shown as follows :- 01 8.0 02 8.0 03 <A Blank Row> 04 8.0 05 <A Blank Row> 06 <A Blank Row> 07 9.0 08 <A Blank Row> 09 9.0 10 9.0 11 <A Blank Row> 12 6.5 13 6.5 14 <A Blank Row> 15 <A Blank Row> 16 <A Blank Row> 17 8.0 18 <A Blank Row> 19 8.0 20 <A Blank Row> 21 8.0 22 <A Blank Row> 23 6.5 24 <A Blank Row> 25 ...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

2nd try, parameter values
I'm having problems posting... sorry if this comes up twice. I have parameter values set to make things easier for the users... The prompt pulls Last Name or Emp #. If the Emp# is not available, is there a way to search by last name, including suffixes, but only entering the last name? or even just the first letter of the last name? On Thu, 19 Jul 2007 09:31:24 -0700, melinda.pluma@agedwards.com wrote: >I'm having problems posting... sorry if this comes up twice. > >I have parameter values set to make things easier for the users... > >The prompt pulls Last Name or...

word freezes periodically when i am tracking changes, using endnote and inserting pictures from file
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel word freezes periodically when i am tracking changes and using endnote and when i am inserting pictures from file. i seem to have no other options but force quit then i get lots of recovered documents. i am doing doctoral study so my documents have endnote field notes and changes highlighted. <br><br>inserting pictures from file into a word document causes instant hanging. Judging from the array of causes you're listing I believe there may be several things that need to be addressed. Sinc...

Step change charts
Help, I am trying to create a bar charts by year but only want to show increases and decreases through the middle years as step changes i.e. 1999 100 2000 +10 2001 -30 2002 +40 2003 120 How can I do this? What I should see is the first vertical bar being 100 then from the 100 level an increase of 10 to get to 110 then a decrease bar of -30 (from the 110 level) to get to 80 etc etc. For a tutorial, see the Excel | Charts | Waterfall Chart page of my web site. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Cus...

How can I change the Z-axes of my window or dialog? #2
Hello, All: I am now meetting a problem that the sub-window( or sub-dialog) would not be shown out in front of the parent window, however, I just wanna let it be shown in front of my parent window. Is there anybody who knows how I can set the Z-axes with any function? Thanks in advance! ...

Inventory Changes on Item Property Screen
Is there away to disable the changing of inventory numbers on the Item Propoerties screen. It is way to easy for my employees to make a careless error and inadvertantly change a number. Also, when I copy an item to create a new one, it takes the inventory # of the old item and moves it to the new one, and I need to remember to change it....Can this be disabled? Are you talking about the On Hand Quantity, or the Item Lookup Code? In either case, you can disable the fields using the Security features of RMS. The security features are based on the "Security Level" field in Cas...

How do I change margins from imperial to metric?
How do I change margins from imperial to metric? I'm using Excel 2002 sp2. Any help appreciated. Jojo This is a Windows OS setting, not an Excel setting. You would go to Windows Control Panel>Regional and Language Options>Regional Options tab>Customize and set the "measurement system" to Metric. This option will affect all programs. Gord Dibben Excel MVP On Mon, 25 Oct 2004 07:29:10 -0700, "Jojo" <Jojo@discussions.microsoft.com> wrote: >How do I change margins from imperial to metric? >I'm using Excel 2002 sp2. Any help appreciated. ...

Changing position of OK and Cancel buttons on CPropertySheet
I found out how to eliminate the Apply button on the property sheet. I want to make the OK button be in the lower right corner and the Cancel button just to the left of it. By default the Cancel button is in the lower right corner and the OK button is just to the left of it. How do I make it the way I want it? Thanks, Ken SetWindowPos/MoveWindow joe On Tue, 27 Jul 2004 11:07:08 -0400, "Ken->" <handsom@comcast.netspicedham> wrote: >I found out how to eliminate the Apply button on the property sheet. I want >to make the OK button be in the lower right corner ...

Changing bar graph colours according to value
Is there any way to have the colours of the bars in a bar graph change according to value? Hi, Have a look at Jon's example. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy nikki8327 wrote: > Is there any way to have the colours of the bars in a bar graph change > according to value? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Access autonumber field changes to date format when Excel imports
When importing Access data into Excel, the autonumber field in Access is chenged to date format instead of a general number format. I could run a macro to change the number format but this only happens on some Vista machines, not all. Any suggestions as to why this is happening? ...

Need to format text in header, but value is generated using VBA
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6360C.2372C770 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I wanted to reference a cell value in my spreadsheet header and found = out how to do that using this function: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim WS As Worksheet For Each WS In Worksheets ActiveSheet.PageSetup.RightHeader =3D _ Format(Worksheets("Time Period Info").Range("B3").Value) =20 Next WS End Sub However, this leaves my text for ...

How do I change the $ currency symbol in a balance sheet template
Can someone please help me remove the $ symbol from a template. I've set thre area settings but the 'format' and then 'Cells' option is not open to me. The sheet must be protected. You will have to unprotect the sheet first from the Tools>Protection>unprotect sheet. If it asks for a password, then probably you are not meant to fiddle around with the format of the template! "Dave" wrote: > Can someone please help me remove the $ symbol from a template. > > I've set thre area settings but the 'format' and then 'Cells' opt...

change toolbar depending on sign in and sign out
Hi I am creating a toolbar in ATL,COM and WTL.Now I want to add the feature of changing the toolbar depending on sign in and sign out.after sign in if the user is a registered one then he should see some buttons which are not present at the time when the user is not signed in.Means I want to add the feature which is already present in yahoo toolbar.But how to do it?How can I trap whether user is signed in or not? ...

error changing data type
I have a 500-MB access 2002 database with several numeric fields of type Double. I'm trying to convert the double fields to decimal fields. I get the following error when saving the table after applying the field change: Microsoft Access can't change the data type. There isn't enough disk space or memory. I have 9 GB free on my hard drive, and I have 512 MB of RAM. What do I need to do to convert these fields? Thanks. I forgot to mention that I'm running Access 2002 sp2. Copy the table without carrying the data across, creating an empty table. Make the changes in...

New Word template always prompts to save changes
Word 2003/XP Pro I am trying to create an alternative Normal.dot. Problem is when I try to save a document created from AltNormal, I always receive the prompt: "Do you want to save the changes to AltNormal.dot?". Typing and saving a few of characters is sufficient to cause the prompt. If I accept the prompt, the prompt returns when I next modify the document. All this occurs even if AltNormal has not been modified from Normal.dot. I have tried many different ways to resolve this including basing AltNormal on: (1) a copy of Normal.dot, (2) a clean document from Norm...

How to avoid when linked source data is changed, chart turns blan.
When I created new charts in Excel 2003, each previous chart became blank. Therefor I saved the file unter a new name and copied the previous chart to the new file. This created ofcourse links to the other files. Now I want to change the source file, but when I do that the chart becomes blank as well. Anybody who had this problem before and found out how to solve it ? Rgds, Chris ...

IE7 won't change the opacity of <tbody>
Hi everybody -- This problem is with IE7 (haven't tried with IE6 or IE8) and the problem page is: http://www.sellmycalls.com/no-fade.html There is the table <table id="main" .. > at line 579 of the source. In that table, there is <tbody id="tbody1"> at line 806. I can change the opacity of the table just fine in IE7. And I can change the opacity of both the <table> and the <tbody> in FF3.5 and Safari. But in IE7 I cannot change the opacity of the <tbody>, which I need to do. The JavaScript function submit( ) that does ...

Excel change case
If you want to change case in Microsoft Excel at a click of a button without inserting any formulas just try this add in http://exceltemplates.blogspot.com/2007/09/microsoft-excel-change-case.html Thanks a lot for the link, this is most probably one of the best I ever got to solve a lot of writing without formula. I'm sure a lot of people will appreciate your kindness Mr. Cricket. Thanks again. "Mr.Cricket" wrote: > If you want to change case in Microsoft Excel at a click of a button > without inserting any formulas just try this add in > http://exceltemplates.blo...

chart type keeps changing
My chart is "embedded" in my worksheet. The worksheet has months across the top in one row and net income per share in a row below it for each corresponding month. The x-axis is pulling data for the month and year eg. Jan-04...Feb-06. The y-axis is pulling data from a different row for the net income for each month. The chart has been maintained since Jan-04. It is a standard clustered column w/ a 3-D visual effect. I am trying to enter data for March '06 in data source, series one. I am updating the series 1 with the new column address that represents the new month eg...

search and return cell value
I hope that someone may be able to help. I have a worksheet that has 7 fixed columns and a variable number of rows. The rows can range from 1 to many. Cell A1 is dependent upon the value in the last row in column G. I can locate this with nested "if" statements or by creating a new column H that will test to see if the next row contains values. Is there any function that I can put in cell A1 that will locate the last row in column G that contains a value and return the value of this cell? Many Thanks Hi if you have no blank rows in between try =OFFSET($G$1,COUNTA(G:G)-1,0) -- Reg...