Macro VBA help needed

C

CarlosAntenna

I recorded these two macros to increment / decrement a date used in a
database query. I assigned them to two buttons that users can use to page
through sales activity one day at a time.

Now I want to limit the range of dates available.

For the NextDate macro, if the resulting date is greater than "today", I
want it to revert to "today".

For the PrevDate macro, if the resulting date is less than "today -14", it
should stay at "today-14".

I don't know enough VBA to make it happen (I just use the recorder), but I
know there are many here who can do it.

Cell B1=1, Cell C1 contains the date being changed.
--
Carlos
=================================================

Sub NextDate()
'
' NextDate Macro
' Macro recorded 01/12/2006 by Carlos
'

'
Range("B1").Select
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd,
SkipBlanks _
:=False, Transpose:=False
End Sub
----------------------------------------------------------------------------
Sub PrevDate()
'
' PrevDate Macro
' Macro recorded 01/12/2006 by Carlos
'

'
Range("B1").Select
Selection.Copy
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlSubtract, _
SkipBlanks:=False, Transpose:=False
End Sub
 
G

Guest

Here is some revised VBA code: I am changing the cell values directly; do not
need to use the 1 in B1:
Sub NextDate()
'
' NextDate Macro
'
Range("C1").Value = Range("C1") + 1
If Range("C1").Value > Date() Then Range("C1").Value = Date()
End Sub

Sub PrevDate()
'
' PrevDate Macro
'
Range("C1").Value= Range("C1").Value - 1
If Range("C1").Value < (Date() - 14) Then Range("C1").Value = Date() - 14
End Sub
 
C

CarlosAntenna

Thanks K,

I knew there was a way to do that, but when you use the recorder for macros
you can only do so much.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top