G
Greg
I have a workbook that I import data into and then manipulate the
data. For each worksheet added I wish to keep a counter of the total
data entry worksheets as I have several other worksheets and graphs as
well.
Currently I simply update a variable that has a public declaration -
NumSites
ie Public Const NumSites As Integer = 19
Modual 1 shows how I use the variable.
However, I wish to automate the process. All worksheets entered are
to the left of a file called FileNames.
I have a Menu added, with macro's so anyone can add more worksheets.
1. What is the most effective way to update the file list. I
currently run a fuction, in Modual 2 where I have declared all my
Public variables and the fucntion that counts the number of worksheets
each time NumSites.
2. I also wish to view the variable NumSites whilst debugging - is
there a way of having a variable hold the value of NumSites so I can
see the value? I have tried declaring a variable as Check_Numsites =
NumSites in the public declaration modual but this fails.
Thank you
------------------------------------------------------------------------------
==================== Modual 2 ========================================
Option Explicit
Public FileName As Variant
Public ChartName As Variant
Public Const FileLocation As String _
= "C:\$user\Assessments\GOHS audits\"
Public Const Tot_Rows As Integer = 187
'======================================
Public Function NumSites()
Dim Numsites_cnt As Integer
Dim WS_public_cnt
For Each WS_public_cnt In Worksheets
Do While Not WS_public_cnt.Name = "File names"
Numsites_cnt = Numsites_cnt + 1
Exit Do
Loop
Next WS_public_cnt
End Function
Modual 1 =========================================================
Sub M1()
MsgBox "This will go through all the worksheets and calculate
compliance as a %" _
& Chr(13) & "in the background and display the percentages as a
group" _
& Chr(13) & "All worksheets are then displayed in outstanding item
order"
Dim last_site As String
Dim Cnt As Integer
Dim Answer As String
Dim Message As String
Dim Title As String
'Dim vbOkCancel As Integer
Dim i As Integer
i = NumSites
Sheets("File names").Select
[a1].Select
Range(Selection, Selection.End(xlDown)).Rows.Select
Cnt = Range(Selection, Selection.End(xlDown)).Rows.Count
Message = ActiveCell.Offset(1, 0)
Do While NumSites <> Cnt
Answer = MsgBox("Do you want to delete upto the following " &
Message, vbOkCancel, Title)
If Answer = vbOK Then
Range(Cells(NumSites + 1, 1), Cells(Cnt, 4)).Select
End If
Exit Do
Loop
For Cnt = 1 To NumSites
Worksheets(Cnt).Select
Call CalcCompliance
Next Cnt
Call addPercentateForGroups
'Makes the groups for the graphs replaces ANZaveCalc
Call M2
Worksheets("Percentage").Select
End Sub
data. For each worksheet added I wish to keep a counter of the total
data entry worksheets as I have several other worksheets and graphs as
well.
Currently I simply update a variable that has a public declaration -
NumSites
ie Public Const NumSites As Integer = 19
Modual 1 shows how I use the variable.
However, I wish to automate the process. All worksheets entered are
to the left of a file called FileNames.
I have a Menu added, with macro's so anyone can add more worksheets.
1. What is the most effective way to update the file list. I
currently run a fuction, in Modual 2 where I have declared all my
Public variables and the fucntion that counts the number of worksheets
each time NumSites.
2. I also wish to view the variable NumSites whilst debugging - is
there a way of having a variable hold the value of NumSites so I can
see the value? I have tried declaring a variable as Check_Numsites =
NumSites in the public declaration modual but this fails.
Thank you
------------------------------------------------------------------------------
==================== Modual 2 ========================================
Option Explicit
Public FileName As Variant
Public ChartName As Variant
Public Const FileLocation As String _
= "C:\$user\Assessments\GOHS audits\"
Public Const Tot_Rows As Integer = 187
'======================================
Public Function NumSites()
Dim Numsites_cnt As Integer
Dim WS_public_cnt
For Each WS_public_cnt In Worksheets
Do While Not WS_public_cnt.Name = "File names"
Numsites_cnt = Numsites_cnt + 1
Exit Do
Loop
Next WS_public_cnt
End Function
Modual 1 =========================================================
Sub M1()
MsgBox "This will go through all the worksheets and calculate
compliance as a %" _
& Chr(13) & "in the background and display the percentages as a
group" _
& Chr(13) & "All worksheets are then displayed in outstanding item
order"
Dim last_site As String
Dim Cnt As Integer
Dim Answer As String
Dim Message As String
Dim Title As String
'Dim vbOkCancel As Integer
Dim i As Integer
i = NumSites
Sheets("File names").Select
[a1].Select
Range(Selection, Selection.End(xlDown)).Rows.Select
Cnt = Range(Selection, Selection.End(xlDown)).Rows.Count
Message = ActiveCell.Offset(1, 0)
Do While NumSites <> Cnt
Answer = MsgBox("Do you want to delete upto the following " &
Message, vbOkCancel, Title)
If Answer = vbOK Then
Range(Cells(NumSites + 1, 1), Cells(Cnt, 4)).Select
End If
Exit Do
Loop
For Cnt = 1 To NumSites
Worksheets(Cnt).Select
Call CalcCompliance
Next Cnt
Call addPercentateForGroups
'Makes the groups for the graphs replaces ANZaveCalc
Call M2
Worksheets("Percentage").Select
End Sub