can i define the Public contant WB and Sheet name for use in form ,macro and sub?

[rrosa1]

hi
i code some macro and desing by help of this forum.but i have some silly question to define the WB and sheet name at one place and that can be use across the macro and userform of WB so i don't have to change when WB name change or sheet name change.

[mdmackillop]

You can use the Code name of a sheet which will not change e.g
Sheet1.Cells(1,1)
I don't believe you can do the same with a workbook.

[rrosa1]

hi sir my code in userform is like this and i have to change the WB name at 8 places, is there any way i can change at one place and it took at another place .
thanks for help

[VBA]Private Sub cmdOK_Click()
Dim i As Long
Dim NSname As Worksheet
Dim Sheetname As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

If Not ComboBox1.ListIndex = -1 Then
Sheetname = ComboBox1.Value
'change file name every month
Workbooks.Open Filename:= _
"C:\Documents and Settings\My Documents\2010\05-10.xls"
Windows("Check In Data.xls").Activate
Sheets("Yesterday").Select
'change file name every month
Sheets("Yesterday").Copy after:=Workbooks("05-10.xls").Sheets(Workbooks("05-10.xls").Sheets.Count)
ActiveSheet.Name = Sheetname
ActiveSheet.Unprotect Password:=""
ActiveSheet.Range("A3").Value = Sheetname
ActiveSheet.Range("K3").Value = "Reporte Generaretd"
ActiveSheet.Range("L3").Value = Now()
ActiveSheet.Protect Password:=""
'change file name every month
Workbooks("05-10.xls").Close SaveChanges:=True
Unload Me
Else
Unload Me
MsgBox "Pick a sheet!", 0, vbNullString
End If


Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True



End Sub


Private Sub UserForm_Initialize()
Dim aryMonths As Variant
Dim i As Long

aryMonths = Array("01", "02", "03", "04", "05", "06", "07", _
"08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31")
For i = LBound(aryMonths) To UBound(aryMonths)
If Not ShExists(aryMonths(i)) Then
Me.ComboBox1.AddItem aryMonths(i)
End If
Next
End Sub



Function ShExists(ByVal ShName As String, _
Optional WB As Workbook, _
Optional CheckCase As Boolean = False) As Boolean

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False



If WB Is Nothing Then
'change file name every month
Set WB = Workbooks.Open("C:\Documents and Settings\Srusty\My Documents\Check In\2010\05-10.xls")
End If

If CheckCase Then
On Error Resume Next
ShExists = CBool(WB.Worksheets(ShName).Name = ShName)
On Error GoTo 0
Else
On Error Resume Next
ShExists = CBool(UCase(WB.Worksheets(ShName).Name) = UCase(ShName))
On Error GoTo 0
End If
'change file name every month
Workbooks("05-10.xls").Close SaveChanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True


End Function
[/VBA]

[mdmackillop]

You can use Constants and Public Variables

[VBA]
Const wbName = "C:\Test\Mybook.xls"
Dim wb As Workbook

Sub Test1()
Set wb = Workbooks.Open(wbName)
Test2
End Sub

Sub Test2()
wb.Sheets(1).Cells(1, 1) = "My Text"
wb.Close True
End Sub

[/VBA]

[Bob Phillips]

[vba]

Private wb As Workbook
Const FILE_PATH As String = "C:\Documents and Settings\Srusty\My Documents\Check In\"
Const FILE_NAME As String = "2010\05-10.xls"

Private Sub cmdOK_Click()
Dim i As Long
Dim NSname As Worksheet
Dim Sheetname As String
Dim wb As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

If Not ComboBox1.ListIndex = -1 Then
Sheetname = ComboBox1.Value
'change file name every month
Set wb = Workbooks.Open(Filename:=File_PATH & FILE_NAME)
Windows("Check In Data.xls").Activate
With Sheets("Yesterday")

.Copy after:=wb.Sheets(wb.Sheets.Count)
.Name = Sheetname
.Unprotect Password:=""
.Range("A3").Value = Sheetname
.Range("K3").Value = "Reporte Generaretd"
.Range("L3").Value = Now()
.Protect Password:=""
End With
'change file name every month
wb.Close SaveChanges:=True
Unload Me
Else
Unload Me
MsgBox "Pick a sheet!", 0, vbNullString
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True

End Sub

Private Sub UserForm_Initialize()
Dim aryMonths As Variant
Dim i As Long

aryMonths = Array("01", "02", "03", "04", "05", "06", "07", _
"08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31")
For i = LBound(aryMonths) To UBound(aryMonths)
If Not ShExists(aryMonths(i)) Then
Me.ComboBox1.AddItem aryMonths(i)
End If
Next
End Sub



Function ShExists(ByVal ShName As String, _
Optional CheckCase As Boolean = False) As Boolean

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Set wb = Workbooks.Open(FILE_PATH & FILE_NAME)
If CheckCase Then
On Error Resume Next
ShExists = CBool(WB.Worksheets(ShName).Name = ShName)
On Error Goto 0
Else
On Error Resume Next
ShExists = CBool(UCase(WB.Worksheets(ShName).Name) = UCase(ShName))
On Error Goto 0
End If
'change file name every month
wb.Close SaveChanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True


End Function
[/vba]

[rrosa1]

thanks xld
but it give compile error
variable not defined

in Function FILE_PATH

[Bob Phillips]

There is no function FILE_PATH. Do you have code before this?

[rrosa1]

hi xld
the following code suppose to open the WB 05-10.xls and create the worksheet in that work book with file name as the combobox value` insted of that it copy "yesterday" but not rename the sheet name as combobox value
now it rename in the active WB where i am run this code so pl point out where i have to change the code to do the thing thanks in advance.

[VBA]If Not ComboBox1.ListIndex = -1 Then
Sheetname = ComboBox1.Value
'change file name every month
Set wb = Workbooks.Open(Filename:=File_PATH & FILE_NAME)
Windows("Check In Data.xls").Activate
With Sheets("Yesterday")

.Copy after:=wb.Sheets(wb.Sheets.Count)
.Name = Sheetname
.Unprotect Password:=""
.Range("A3").Value = Sheetname
.Range("K3").Value = "Reporte Generaretd"
.Range("L3").Value = Now()
.Protect Password:=""
End With
'change file name every month
wb.Close SaveChanges:=True
Unload Me
Else
Unload Me
MsgBox "Pick a sheet!", 0, vbNullString
End If [/VBA]

[rrosa1]

hi xld
the code copy and creating sheet in WB FILE_NAME 05-10.xls as
"Yesterday",than when i run the code agine it copy and creating sheet as "Yesterday(1)" but not using the combobox value of sheet name.
pl help buddy
my old code was like this was doing the job as i explain but need some thing to active the newly copied sheet in 05-10.xls and rename the same as combobox value

[vba]If Not ComboBox1.ListIndex = -1 Then
Sheetname = ComboBox1.Value
'change file name every month
Workbooks.Open Filename:= _
"C:\Documents and Settings\My Documents\2010\05-10.xls"
Windows("Check In Data.xls").Activate
Sheets("Yesterday").Select
'change file name every month
Sheets("Yesterday").Copy after:=Workbooks("05-10.xls").Sheets(Workbooks("05-10.xls").Sheets.Count)
ActiveSheet.Name = Sheetname
ActiveSheet.Unprotect Password:=""
ActiveSheet.Range("A3").Value = Sheetname
ActiveSheet.Range("K3").Value = "Reporte Generaretd"
ActiveSheet.Range("L3").Value = Now()
ActiveSheet.Protect Password:=""
'change file name every month
Workbooks("05-10.xls").Close SaveChanges:=True
Unload Me
Else
Unload Me
MsgBox "Pick a sheet!", 0, vbNullString
End If [/vba]