Calling procedures from within VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Excel Helper

I have a problem that I have never encountered before and could use some help.

In simple terms I want to run a procedure that calls another procedure
within that code. The procedure allows a worksheet to be added to a workbook,
with the user specifying a name via InputBox. However, before the worksheet
is added the procedure checks the 'value' of a given cell (cell S3). If that
= "ON" then some of the figures on the existing worksheet are changed
(divided by 10) and a menu item on a custom menu is updated (has a tick mark
next to it using msoButtonDown).

My code looks something like this:

Public Sub InsertSheet()
Dim answer As String

If Worksheets(Worksheets.Count - 2).Range("S3") = "ON" Then
Call ToggleRunRate
End If

answer = InputBox("Enter worksheet name", "Add Sheet")

....more code here...

End sub

The problem that I am having is as follows.

If cell S3 is not equal to 'ON' then the code works fine and I get a prompt
for the InputBox and all subsequent code does what it ought to.

If cell S3 equals 'OFF' then the procedure 'ToggleRunRate' executes as it
should but the InputBox does not appear. It is as if the procedure cannot
progress any further than Call ToggleRunRate, even though no error is
generated.

Some things that I would add that may help:

(1) I have checked the state of Application.ScreenUpdating to see if that is
preventing execution but all seems fine.
(2) The procedure ToggleRunRate is in a separate module. I have defined it
as Public. I don't see why this would make a difference?

I have never had this issue before. All I can think of is that because of
the operation that ToggleRunRate performs i.e. makes some calculations on a
worksheet and amends the appearance of a menu item, that this is in some way
interfering with the code?

I wonder if anybody has any suggestiins on this? I can give more details if
needed.

Regards


Alex Park
 
I think we would need to see ToggleRunRate to help on this, it would seem to
be a problem in there.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Bob

Sorry for delay in replying. There are 3 pieces of code that you might need
to see. As a high level summary:

(1)ToggleRunRate - If this procedure is called it loops through a number of
cells on the worksheet and either divides by a number (if ToggleState=OFF) or
multiplies by a number (if ToggleState=ON). This procedure then calls
CheckToggleRunRates. [NB the Call createArrays executes another procedure
that sets up some arrays with column and row numbers in to make the refernces
to cells work]

(2)CheckToggleRunRates - this sets the 'tick mark' on ther custom menu item
that I have built depending whether state is 'ON' or 'OFF'.

(3) Class Module - this monitors application wide events so that the menu
item is alwsys et to the correct state. I admit that I took this code
directly from a book and am a bit out of my depth of whether this is wholly
necessary or not.

1 )
Public Sub ToggleRunRate()
Application.ScreenUpdating = False
Dim sht As Long
Dim ToggleState As Range
Dim RunRate As Range
Dim ind As Long

ind = ActiveWorkbook.ActiveSheet.Index

Set ToggleState = Range("S3")

Call createArrays

If ToggleState = "OFF" Then

For sht = 2 To Worksheets.Count - 2
Worksheets(sht).Select
Worksheets(sht).Range("S3") = "ON"
Set RunRate = Range("M3")
For j = 0 To UBound(psRRCOLS)
For i = 0 To UBound(rrRWS)
For cnt = 0 To 8
Cells(rrRWS(i) + cnt, psRRCOLS(j)) = Cells(rrRWS(i) + cnt,
psRRCOLS(j)) / RunRate
Next cnt
Next i
Next j
Next sht
Call CheckToggleRunRates
End
End If

If ToggleState = "ON" Then

For sht = 2 To Worksheets.Count - 2
Worksheets(sht).Select
Worksheets(sht).Range("S3") = "OFF"
Set RunRate = Range("M3")
For j = 0 To UBound(psRRCOLS)
For i = 0 To UBound(rrRWS)
For cnt = 0 To 8
Cells(rrRWS(i) + cnt, psRRCOLS(j)) = Cells(rrRWS(i) + cnt,
psRRCOLS(j)) * RunRate
Next cnt
Next i
Next j
Next sht
Call CheckToggleRunRates
End
End If

Worksheets(ind).Select
Application.ScreenUpdating = True
End Sub

2)
Public Sub CheckToggleRunRates()
Dim ToggleRR As CommandBarButton
On Error Resume Next
Set ToggleRR = CommandBars(1).Controls("Data Manager").Controls("Run Rate")
If Range("S3") = "ON" Then
ToggleRR.State = msoButtonDown
Else
ToggleRR.State = msoButtonUp
End If
End Sub

3)

Public WithEvents AppEvents As Excel.Application

Private Sub AppEvents_SheetActivate(ByVal Sh As Object)
Call CheckToggleRunRate
End Sub

Private Sub AppEvents_WindowActivate(ByVal wb As Workbook, ByVal Wn As Window)
Call CheckToggleRunRate
End Sub

Private Sub AppEvents_WorkbookActivate(ByVal wb As Workbook)
Call CheckToggleRunRate
End Sub

I don't know if this is of use. At any rate, going back to my original post,
this code will execute fine when called from another procedure but any
subsequent is not executed. This is what I am struggling with.

I appreciate any time that you can set aside for this problem.

Regards


Alex Park
 
Back
Top