excel vba : references to objects from other worksheets/workbooks

[browserice]

I am starting on Excel VBA as of today. I have this Excel file at the office with multiple worksheets in it. We use it to manualy add data on daily worksheets (one for each day of the month) and then take the totals of that daily worksheet to copy to another worksheet (same excel file) where all daily totals are used for a monthly overview and in a graph.

The problem with this is that we have to manualy change this excel file according to the month we want to use it for. So we have to rename all daily worksheets to match each days of the current month, change all month names mentions in all worksheets, ...

I want to create VBA codes in this excel file so that when we open it, the setup worksheet will be used to enter what month we want to use this workbook (that's how you call this excel file, right ?) and have it automaticaly create all necessary worksheets and change what ever infos are needed. I have a general idea on how to do this.

But since I am starting on VBA, I need to know what is the syntax to refer to the followings the general code region :
- object-a on worksheet-x
- method of object-b on worksheet-z
- worksheet-w
- cell-z on worksheet-t

I am not sure on how to code the references.

 

[browserice]

Here is an exemple (have plenty of other typs of code I wanna do but this one is easy enough to show you what I need).

I have in the general code area :
Private Sub Workbook_Open()
' code to put here for initialization
End Sub

Now in this workbook, I have many worksheets but on the Setup worksheet, there is this combobox where I want to have it initialized with data that is related to the current date (previous month, current month, next month). This is to be done everytimes this excel file is opened. The selection value made will then be used by the code to setup the existing worksheets and all the new ones to be created.

How do I put the code in there to call that Setup combobox object to have it execute its initialize method ?

worksheet Setup
- object cbox_SelectDORMonth
-following code is for that combo box
Public Sub cbox_SelectDORMonth_Create()
Dim prev_date As Date
Dim next_date As Date
Dim cur_month As Integer
Dim cur_year As String

cur_month = Month(Now)
cur_year = Format(Now, "yyyy")

' to handle february case
Select Case cur_month
Case (2)
prev_date = DateValue("01/01/" & cur_year)
next_date = DateValue("03/01/" & cur_year)
Case (3)
prev_date = DateValue("02/01/" & cur_year)
next_date = DateValue("04/01/" & cur_year)
Case Else
prev_date = Now - 30
next_date = Now + 30
End Select

cbox_SelectDORMonth.Clear
cbox_SelectDORMonth.AddItem Format(prev_date, "mmmm yyyy")
cbox_SelectDORMonth.AddItem Format(Now, "mmmm yyyy")
cbox_SelectDORMonth.AddItem Format(next_date, "mmmm yyyy")

End Sub