I am trying to set up a simulation that will calculate the position and
velocity of an object after the user sets the initial conditions, but I am
running into snags.
Snags: (I have put * in the code for each snag)

1) Workbook(1).Activate I need to lock all of the cells except a few
that the user may change to set the initial conditions. Is this not the
proper syntax to activate the whole workbook?

2) The values did not show up in the cells (time, position, vel, and acc). I
used range("D"& row), where row is an integer set in the code. Will this
work, or do I need to first change the integer to a string? (Does VBA
automatically convert data types when necessary?)

3) Is there a preferred method to lock all of the cells? I have chosen to
activate the whole workbook and then set the property of the active
selection.

' Sub Worksheet_Change(ByVal Target As Range) ' this will trigger if any
part of the sheet is changed
Sub One_D_Motion()

Dim time1 As Single
Dim vInit1 As Single
Dim acc1 As Single
Dim xInit1 As Integer
Dim acc2 As Single
Dim x As Single
Dim vel As Single
Dim row As Integer

' This will find the position and velcoity for the first 20 seconds
' The user must set the initial conditions and the time period for the
accelerations
' Written 6/12/2008 by Jeff Ciaccio
'

' Check to see if the values are in range. If not, prompt the user for
another number.

*1) Workbook(1).Activate 'Lock all of the cells to protect them
from chages
*3) Selection.Locked = True
*3) Selection.FormulaHidden = False

Range("B1:B4,B6").Select 'Unlock only the cells the user may change
Range("B6").Activate
Selection.Locked = False
Selection.FormulaHidden = False

'Protect the sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Range("B1").Select ' Check initial position
With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="-5000", Formula2:="5000"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Position"
.ErrorTitle = ""
.InputMessage = "Enter a whole number between -5000 and +5000. "
.ErrorMessage = "Too Far!!"
.ShowInput = True
.ShowError = True
End With

Range("B2").Select ' Check initial velocity
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator
_
:=xlBetween, Formula1:="-100", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Velocity"
.ErrorTitle = "Too Fast!!"
.InputMessage = "Put in a number between -100 and 100"
.ErrorMessage = "Put in a number between -100 and 100"
.ShowInput = True
.ShowError = True
End With

Range("B3").Select ' Check initial acceleration
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator
_
:=xlBetween, Formula1:="-20", Formula2:="20"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Acceleration"
.ErrorTitle = "Too Much Force!!"
.InputMessage = "Put in a number between -20 and 20"
.ErrorMessage = "Put in a number between -20 and 20"
.ShowInput = True
.ShowError = True
End With

Range("B4").Select ' Check initial time period
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator
_
:=xlBetween, Formula1:="0.1", Formula2:="19.9"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "First time period"
.ErrorTitle = "Too Fast!!"
.InputMessage = "Put in a number between -100 and 100"
.ErrorMessage = "Put in a number between -100 and 100"
.ShowInput = True
.ShowError = True
End With

' Record the values the user has chosen
time1 = Range("b4").Value
vInit1 = Range("b2").Value
acc1 = Range("b3").Value
xInit1 = Range("b2").Value
acc2 = Range("b6").Value

' Set the first row cells
Time = 0
row = 2
Range("D2").Value = 0
Range("E2").Value = vInit1
Range("f2").Value = acc1
Range("d3").Select

Do Until Time time1
Time = Time + 0.1
row = row + 1
x = xInit1 + vInit1 * Time + 1 / 2 * acc1 * Time ^ 2
vel = vInit1 + vInit1 * acc1
*2) Range("d" & row).Value = Time
*2) Range("e" & row).Value = vel
*2) Range("f" & row).Value = acc1
Loop

End Sub


--
Jeff Ciaccio
Physics and AP Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio

Jeff -

In the other group I suggested using worksheet formulas. Check your email,
because I built a little model for you and sent it off. I plan to use it as
a blog entry sometime soon.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jeff Ciaccio" wrote in message
...
I am trying to set up a simulation that will calculate the position and
velocity of an object after the user sets the initial conditions, but I am
running into snags.
Snags: (I have put * in the code for each snag)

1) Workbook(1).Activate I need to lock all of the cells except a
few that the user may change to set the initial conditions. Is this not
the proper syntax to activate the whole workbook?

2) The values did not show up in the cells (time, position, vel, and acc).
I used range("D"& row), where row is an integer set in the code. Will this
work, or do I need to first change the integer to a string? (Does VBA
automatically convert data types when necessary?)

3) Is there a preferred method to lock all of the cells? I have chosen to
activate the whole workbook and then set the property of the active
selection.

' Sub Worksheet_Change(ByVal Target As Range) ' this will trigger if any
part of the sheet is changed
Sub One_D_Motion()

Dim time1 As Single
Dim vInit1 As Single
Dim acc1 As Single
Dim xInit1 As Integer
Dim acc2 As Single
Dim x As Single
Dim vel As Single
Dim row As Integer

' This will find the position and velcoity for the first 20 seconds
' The user must set the initial conditions and the time period for the
accelerations
' Written 6/12/2008 by Jeff Ciaccio
'

' Check to see if the values are in range. If not, prompt the user for
another number.

*1) Workbook(1).Activate 'Lock all of the cells to protect them
from chages
*3) Selection.Locked = True
*3) Selection.FormulaHidden = False

Range("B1:B4,B6").Select 'Unlock only the cells the user may change
Range("B6").Activate
Selection.Locked = False
Selection.FormulaHidden = False

'Protect the sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Range("B1").Select ' Check initial position
With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="-5000", Formula2:="5000"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Position"
.ErrorTitle = ""
.InputMessage = "Enter a whole number between -5000 and +5000. "
.ErrorMessage = "Too Far!!"
.ShowInput = True
.ShowError = True
End With

Range("B2").Select ' Check initial velocity
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween, Formula1:="-100", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Velocity"
.ErrorTitle = "Too Fast!!"
.InputMessage = "Put in a number between -100 and 100"
.ErrorMessage = "Put in a number between -100 and 100"
.ShowInput = True
.ShowError = True
End With

Range("B3").Select ' Check initial acceleration
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween, Formula1:="-20", Formula2:="20"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Acceleration"
.ErrorTitle = "Too Much Force!!"
.InputMessage = "Put in a number between -20 and 20"
.ErrorMessage = "Put in a number between -20 and 20"
.ShowInput = True
.ShowError = True
End With

Range("B4").Select ' Check initial time period
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween, Formula1:="0.1", Formula2:="19.9"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "First time period"
.ErrorTitle = "Too Fast!!"
.InputMessage = "Put in a number between -100 and 100"
.ErrorMessage = "Put in a number between -100 and 100"
.ShowInput = True
.ShowError = True
End With

' Record the values the user has chosen
time1 = Range("b4").Value
vInit1 = Range("b2").Value
acc1 = Range("b3").Value
xInit1 = Range("b2").Value
acc2 = Range("b6").Value

' Set the first row cells
Time = 0
row = 2
Range("D2").Value = 0
Range("E2").Value = vInit1
Range("f2").Value = acc1
Range("d3").Select

Do Until Time time1
Time = Time + 0.1
row = row + 1
x = xInit1 + vInit1 * Time + 1 / 2 * acc1 * Time ^ 2
vel = vInit1 + vInit1 * acc1
*2) Range("d" & row).Value = Time
*2) Range("e" & row).Value = vel
*2) Range("f" & row).Value = acc1
Loop

End Sub


--
Jeff Ciaccio
Physics and AP Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio

After seeing Jeff's post, I blogged about building such models, and included
a sample:

http://peltiertech.com/WordPress/200...hysics-lesson/

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
774-275-0064
208-485-0691 fax

http://PeltierTech.com/
_______


"ryguy7272" wrote in message
...
You may find these interesting:
http://www.freefilehosting.net/download/3if11
http://www.freefilehosting.net/download/3if12
http://www.freefilehosting.net/download/3if15
http://www.freefilehosting.net/download/3if16

Finally, take a look at this:
http://www.faculty.virginia.edu/ribando/modules/xls/


Regards,
Ryan---

--
RyGuy


"Jon Peltier" wrote:

Jeff -

In the other group I suggested using worksheet formulas. Check your
email,
because I built a little model for you and sent it off. I plan to use it
as
a blog entry sometime soon.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jeff Ciaccio" wrote in message
...
I am trying to set up a simulation that will calculate the position and
velocity of an object after the user sets the initial conditions, but I
am
running into snags.
Snags: (I have put * in the code for each snag)

1) Workbook(1).Activate I need to lock all of the cells except a
few that the user may change to set the initial conditions. Is this
not
the proper syntax to activate the whole workbook?

2) The values did not show up in the cells (time, position, vel, and
acc).
I used range("D"& row), where row is an integer set in the code. Will
this
work, or do I need to first change the integer to a string? (Does VBA
automatically convert data types when necessary?)

3) Is there a preferred method to lock all of the cells? I have chosen
to
activate the whole workbook and then set the property of the active
selection.

' Sub Worksheet_Change(ByVal Target As Range) ' this will trigger if
any
part of the sheet is changed
Sub One_D_Motion()

Dim time1 As Single
Dim vInit1 As Single
Dim acc1 As Single
Dim xInit1 As Integer
Dim acc2 As Single
Dim x As Single
Dim vel As Single
Dim row As Integer

' This will find the position and velcoity for the first 20 seconds
' The user must set the initial conditions and the time period for the
accelerations
' Written 6/12/2008 by Jeff Ciaccio
'

' Check to see if the values are in range. If not, prompt the user for
another number.

*1) Workbook(1).Activate 'Lock all of the cells to protect
them
from chages
*3) Selection.Locked = True
*3) Selection.FormulaHidden = False

Range("B1:B4,B6").Select 'Unlock only the cells the user may
change
Range("B6").Activate
Selection.Locked = False
Selection.FormulaHidden = False

'Protect the sheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

Range("B1").Select ' Check initial position
With Selection.Validation
.Delete
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop,
_
Operator:=xlBetween, Formula1:="-5000", Formula2:="5000"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Position"
.ErrorTitle = ""
.InputMessage = "Enter a whole number between -5000 and +5000. "
.ErrorMessage = "Too Far!!"
.ShowInput = True
.ShowError = True
End With

Range("B2").Select ' Check initial velocity
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween, Formula1:="-100", Formula2:="100"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Velocity"
.ErrorTitle = "Too Fast!!"
.InputMessage = "Put in a number between -100 and 100"
.ErrorMessage = "Put in a number between -100 and 100"
.ShowInput = True
.ShowError = True
End With

Range("B3").Select ' Check initial acceleration
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween, Formula1:="-20", Formula2:="20"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Initial Acceleration"
.ErrorTitle = "Too Much Force!!"
.InputMessage = "Put in a number between -20 and 20"
.ErrorMessage = "Put in a number between -20 and 20"
.ShowInput = True
.ShowError = True
End With

Range("B4").Select ' Check initial time period
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween, Formula1:="0.1", Formula2:="19.9"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "First time period"
.ErrorTitle = "Too Fast!!"
.InputMessage = "Put in a number between -100 and 100"
.ErrorMessage = "Put in a number between -100 and 100"
.ShowInput = True
.ShowError = True
End With

' Record the values the user has chosen
time1 = Range("b4").Value
vInit1 = Range("b2").Value
acc1 = Range("b3").Value
xInit1 = Range("b2").Value
acc2 = Range("b6").Value

' Set the first row cells
Time = 0
row = 2
Range("D2").Value = 0
Range("E2").Value = vInit1
Range("f2").Value = acc1
Range("d3").Select

Do Until Time time1
Time = Time + 0.1
row = row + 1
x = xInit1 + vInit1 * Time + 1 / 2 * acc1 * Time ^ 2
vel = vInit1 + vInit1 * acc1
*2) Range("d" & row).Value = Time
*2) Range("e" & row).Value = vel
*2) Range("f" & row).Value = acc1
Loop

End Sub


--
Jeff Ciaccio
Physics and AP Physics Teacher
Sprayberry High School; Marietta, GA
Blog: http://sprayberry.typepad.com/ciaccio