Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Using Date in InputBox for criteria in code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 14
Default Using Date in InputBox for criteria in code
I have the following code:

Sub ShadeCells()

Dim dblSum As Double
Dim cell As Range
On Error GoTo Errhandler:
dblSum = 0
With ActiveSheet.Range("A:A")
For Each cell In .Range("D:D")
If cell.Text < "" Then
If cell.Value <= DateValue("2/08/2005") Then
cell.Offset(0, 1).Interior.ColorIndex = 16
If IsNumeric(cell.Offset(0, 1)) Then
dblSum = dblSum + cell.Offset(0, 1).Value
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Next
Application.EnableEvents = False

.Range("G1").Value = dblSum
.Font.Bold = True
.NumberFormat = "$#,##0.00"

Application.EnableEvents = True
End With
Errhandler:
Application.EnableEvents = True
End Sub

Works great, now instead of going in and changing the date every 2 weeks
(this is how often I use this code) I would like an InputBox come up and ask
for the date to use and then use that entry in the criteria section:

If cell.Value <= DateValue("2/08/2005") Then

Any suggestions would be great, Thanks!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 7,247
Default Using Date in InputBox for criteria in code
Try something like

Dim S As String
S = InputBox("Enter A Date")
If S = "" Then
Exit Sub
End If
' your code
If cell.Value <= DateValue(S) Then
' your code


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"SMac" wrote in message
...
I have the following code:

Sub ShadeCells()

Dim dblSum As Double
Dim cell As Range
On Error GoTo Errhandler:
dblSum = 0
With ActiveSheet.Range("A:A")
For Each cell In .Range("D:D")
If cell.Text < "" Then
If cell.Value <= DateValue("2/08/2005") Then
cell.Offset(0, 1).Interior.ColorIndex = 16
If IsNumeric(cell.Offset(0, 1)) Then
dblSum = dblSum + cell.Offset(0, 1).Value
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Next
Application.EnableEvents = False

.Range("G1").Value = dblSum
.Font.Bold = True
.NumberFormat = "$#,##0.00"

Application.EnableEvents = True
End With
Errhandler:
Application.EnableEvents = True
End Sub

Works great, now instead of going in and changing the date
every 2 weeks
(this is how often I use this code) I would like an InputBox
come up and ask
for the date to use and then use that entry in the criteria
section:

If cell.Value <= DateValue("2/08/2005") Then

Any suggestions would be great, Thanks!


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 236
Default Using Date in InputBox for criteria in code
Sub ShadeCells()
Dim dblSum As Double
Dim cell As Range
Dim strAnswer As String '<==== added

On Error GoTo Errhandler:
strAnswer = _
Application.InputBox(Prompt:="Enter Date: ", _
Title:="Use format mm/dd/yyyy...", _
Default:=today(), Type:=2) '<==== added

dblSum = 0
With ActiveSheet.Range("A:A")
For Each cell In .Range("D:D")
If cell.Text < "" Then
If cell.Value <= DateValue(strAnswer) Then '<==== changed

HTH,
Gary Brown


"SMac" wrote:

I have the following code:

Sub ShadeCells()

Dim dblSum As Double
Dim cell As Range
On Error GoTo Errhandler:
dblSum = 0
With ActiveSheet.Range("A:A")
For Each cell In .Range("D:D")
If cell.Text < "" Then
If cell.Value <= DateValue("2/08/2005") Then
cell.Offset(0, 1).Interior.ColorIndex = 16
If IsNumeric(cell.Offset(0, 1)) Then
dblSum = dblSum + cell.Offset(0, 1).Value
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Next
Application.EnableEvents = False

.Range("G1").Value = dblSum
.Font.Bold = True
.NumberFormat = "$#,##0.00"

Application.EnableEvents = True
End With
Errhandler:
Application.EnableEvents = True
End Sub

Works great, now instead of going in and changing the date every 2 weeks
(this is how often I use this code) I would like an InputBox come up and ask
for the date to use and then use that entry in the criteria section:

If cell.Value <= DateValue("2/08/2005") Then

Any suggestions would be great, Thanks!
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 236
Default Using Date in InputBox for criteria in code
oops...
s/b
strAnswer = _
Application.InputBox(Prompt:="Enter Date: ", _
Title:="Use format mm/dd/yyyy...", _
Default:=Format(Now, "mm/dd/yyyy"), Type:=2)

Today doesn't work in vba, changed to Format(Now, "mm/dd/yyyy")

"Gary Brown" wrote:

Sub ShadeCells()
Dim dblSum As Double
Dim cell As Range
Dim strAnswer As String '<==== added

On Error GoTo Errhandler:
strAnswer = _
Application.InputBox(Prompt:="Enter Date: ", _
Title:="Use format mm/dd/yyyy...", _
Default:=today(), Type:=2) '<==== added

dblSum = 0
With ActiveSheet.Range("A:A")
For Each cell In .Range("D:D")
If cell.Text < "" Then
If cell.Value <= DateValue(strAnswer) Then '<==== changed

HTH,
Gary Brown


"SMac" wrote:

I have the following code:

Sub ShadeCells()

Dim dblSum As Double
Dim cell As Range
On Error GoTo Errhandler:
dblSum = 0
With ActiveSheet.Range("A:A")
For Each cell In .Range("D:D")
If cell.Text < "" Then
If cell.Value <= DateValue("2/08/2005") Then
cell.Offset(0, 1).Interior.ColorIndex = 16
If IsNumeric(cell.Offset(0, 1)) Then
dblSum = dblSum + cell.Offset(0, 1).Value
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Next
Application.EnableEvents = False

.Range("G1").Value = dblSum
.Font.Bold = True
.NumberFormat = "$#,##0.00"

Application.EnableEvents = True
End With
Errhandler:
Application.EnableEvents = True
End Sub

Works great, now instead of going in and changing the date every 2 weeks
(this is how often I use this code) I would like an InputBox come up and ask
for the date to use and then use that entry in the criteria section:

If cell.Value <= DateValue("2/08/2005") Then

Any suggestions would be great, Thanks!
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Using Date in InputBox for criteria in code
Date does though.

--

HTH

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


"Gary Brown" wrote in message
...
oops...
s/b
strAnswer = _
Application.InputBox(Prompt:="Enter Date: ", _
Title:="Use format mm/dd/yyyy...", _
Default:=Format(Now, "mm/dd/yyyy"), Type:=2)

Today doesn't work in vba, changed to Format(Now, "mm/dd/yyyy")

"Gary Brown" wrote:

Sub ShadeCells()
Dim dblSum As Double
Dim cell As Range
Dim strAnswer As String '<==== added

On Error GoTo Errhandler:
strAnswer = _
Application.InputBox(Prompt:="Enter Date: ", _
Title:="Use format mm/dd/yyyy...", _
Default:=today(), Type:=2) '<==== added

dblSum = 0
With ActiveSheet.Range("A:A")
For Each cell In .Range("D:D")
If cell.Text < "" Then
If cell.Value <= DateValue(strAnswer) Then '<==== changed

HTH,
Gary Brown


"SMac" wrote:

I have the following code:

Sub ShadeCells()

Dim dblSum As Double
Dim cell As Range
On Error GoTo Errhandler:
dblSum = 0
With ActiveSheet.Range("A:A")
For Each cell In .Range("D:D")
If cell.Text < "" Then
If cell.Value <= DateValue("2/08/2005") Then
cell.Offset(0, 1).Interior.ColorIndex = 16
If IsNumeric(cell.Offset(0, 1)) Then
dblSum = dblSum + cell.Offset(0, 1).Value
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Next
Application.EnableEvents = False

.Range("G1").Value = dblSum
.Font.Bold = True
.NumberFormat = "$#,##0.00"

Application.EnableEvents = True
End With
Errhandler:
Application.EnableEvents = True
End Sub

Works great, now instead of going in and changing the date every 2
weeks
(this is how often I use this code) I would like an InputBox come up
and ask
for the date to use and then use that entry in the criteria section:

If cell.Value <= DateValue("2/08/2005") Then

Any suggestions would be great, Thanks!


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 236
Default Using Date in InputBox for criteria in code
Ohhhhhhhhhh, be quite ;O

Why go with something nice and simple like that when you can get real
complicated? :O

Thanks for the note.
Gary


"Bob Phillips" wrote:

Date does though.

--

HTH

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


"Gary Brown" wrote in message
...
oops...
s/b
strAnswer = _
Application.InputBox(Prompt:="Enter Date: ", _
Title:="Use format mm/dd/yyyy...", _
Default:=Format(Now, "mm/dd/yyyy"), Type:=2)

Today doesn't work in vba, changed to Format(Now, "mm/dd/yyyy")

"Gary Brown" wrote:

Sub ShadeCells()
Dim dblSum As Double
Dim cell As Range
Dim strAnswer As String '<==== added

On Error GoTo Errhandler:
strAnswer = _
Application.InputBox(Prompt:="Enter Date: ", _
Title:="Use format mm/dd/yyyy...", _
Default:=today(), Type:=2) '<==== added

dblSum = 0
With ActiveSheet.Range("A:A")
For Each cell In .Range("D:D")
If cell.Text < "" Then
If cell.Value <= DateValue(strAnswer) Then '<==== changed

HTH,
Gary Brown


"SMac" wrote:

I have the following code:

Sub ShadeCells()

Dim dblSum As Double
Dim cell As Range
On Error GoTo Errhandler:
dblSum = 0
With ActiveSheet.Range("A:A")
For Each cell In .Range("D:D")
If cell.Text < "" Then
If cell.Value <= DateValue("2/08/2005") Then
cell.Offset(0, 1).Interior.ColorIndex = 16
If IsNumeric(cell.Offset(0, 1)) Then
dblSum = dblSum + cell.Offset(0, 1).Value
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Else
cell.Offset(0, 1).Interior.ColorIndex = xlNone
End If
Next
Application.EnableEvents = False

.Range("G1").Value = dblSum
.Font.Bold = True
.NumberFormat = "$#,##0.00"

Application.EnableEvents = True
End With
Errhandler:
Application.EnableEvents = True
End Sub

Works great, now instead of going in and changing the date every 2
weeks
(this is how often I use this code) I would like an InputBox come up
and ask
for the date to use and then use that entry in the criteria section:

If cell.Value <= DateValue("2/08/2005") Then

Any suggestions would be great, Thanks!



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Using Date in InputBox for criteria in code
LOL!

"Gary Brown" wrote in message
...
Ohhhhhhhhhh, be quite ;O

Why go with something nice and simple like that when you can get real
complicated? :O

Thanks for the note.
Gary


"Bob Phillips" wrote:

Date does though.



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
InputBox code baehrjf Excel Programming 0 August 12th 04 11:47 PM
InputBox code baehrjf Excel Programming 0 August 12th 04 07:13 PM
InputBox code Mark Excel Programming 0 August 12th 04 08:25 AM
Using InputBox to change query criteria in Access JohnBoy Excel Programming 2 September 16th 03 02:08 PM
Excel Macro Code invoking InputBox. Andrew Thorne Excel Programming 0 July 30th 03 10:23 PM


All times are GMT +1. The time now is 11:15 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement