macro to add 1 day to date in cell 1

[SharonMee]

Hi there:

I want to write a macro such that on opening the workbook it will go to cell B4 of a sheet called "Summary" and it would add 1 workday to the date in that cell.

So something like, workday(b4,1) and b4 should remain in the date format.

I want this to happen when the workbook is opened:

Private Sub Workbook_Open()

MsgBox "Click OK to refresh workbook"
End Sub


Thanks for helping.


SharonMee

 

[SkipVought]

SharonMee,

If [B4] contains some initial data then maybe [C4] could have a number that gets incrimented in the open event.

Private Sub Workbook_Open()
  [C4] = [C4] + 1
End Sub

Then in your sheet, the date formula would be
[tt]
=[B4]+[C4]
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SharonMee]

Thanks Skip, but I don't really understand. I want the macro to increase B4 by 1 workday, so it will override the date presently in B$ by increasing it by 1 workday.

So, are you saying excel, can't replace the value in B4 automatcially?


Thanks.

SharonMee

 

[SkipVought]

OK, I missunderstood your original criteria

Private Sub Workbook_Open()
  [b4] = workday(Range("B4"), 1)
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SharonMee]

Thanks Skip, how would the code know which sheet to update, say the name of my excel sheet is "Summary". Because I placed the code in the summary sheet in vba, but nothing happened. Then I placed it in This workbook and I am getting a compiler error, sub or function not defined. Thanks for your patience. I am just learning to use vba.

SharonMee

 

[SkipVought]

Private Sub Workbook_Open()
with worksheets("Summary")
.[b4] = workday(.Range("B4"), 1)
end with
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SharonMee]

Hi, thanks again.

I placed the code in ThisWorkbook and I am still getting the message, Compiler error: sub or function not defined, and the "workday " is highlighted.

What can I do?

Thanks.

SharonMee

 

[SkipVought]

In you workbook you have to have the Analysis Toolpack - VBA Add-In (Tools/Addins)

AND

In VB Editor (Tools/references - and select atpvbaen.xls)



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SharonMee]

I can'f find atpvbaen.xls in Tools/reference.

I also checked for Microsoft Excel Atpvbaen, it's not there. Does that mean the workday function can't work with vba on my computer. But the workday function works in a formula in my excel sheet. I wonder if there is a way out.

Thanks for all your help.

SharonMee

 

[TonyJollans]

Hi SharonMee,

Have you checked both the requirements in Skip's post?

Note that there are two different Add-Ins in the WORKSHEET under Tools > Add-Ins:

Analysis ToolPak which provides the Worksheet Functions, and ...

Analysis ToolPak - VBA which provides the VBA Functions.

You must install the Analysis ToolPak - VBA Add-In first. Only then will atpvbaen.xls appear in the list of References.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[SharonMee]

Thanks Skip, it's working now.