Automating Refreshing (Part II)

[Jessica]

Good afternoon again.

I have been tinkering with the code examples that have been provided from
yesterday and I have come to a snag in the road, although it mostly works, I
am unable to get the toggle button to act as a password prompter, I'd
appreciate some assistance with the coding if anyone is able to help. Here
is the code I am using now and when I click the toggle button to prompt for a
password, an error comes up with no block if without end if? I am confused
as to what VB is thinking it should be seeing as there are two if block
statements and two end ifs.

Am I missing something simple?

Private Sub ToggleButton1_Click()
Dim x As String
x = InputBoxDK("Type your password here.", "Password Required", "test")
If x = "test" Then
ToggleButton1.Value = True

If ToggleButton1.Value = True Then
'
'
ToggleButton1.Caption = "Refresh Enabled"
StartTimer
Else
'
'
ToggleButton1.Caption = "Refresh Disabled"
End If
If x <> "test" Then
MsgBox "You didn't enter the correct password to turn off the refresher."
End
End If

End Sub

Thanks for your time with my learning curve.

Jessica

 

[Pflugs]

Jessica,

There are three IF statements in your code, but you put END instead of END
IF for the second closing. That should help. How is the rest of the project
coming? Did you look into changing the entry to stars like in the link I
sent you?

Also, you might be interested in Smart Indenter, a free VBA add in that
helps you clean up your code. Look at it here:
http://www.oaltd.co.uk/Indenter/IndentPage.asp

HTH,
Pflugs

 

[Jessica]

Oh, yes that helped. I've been working on a few projects today, so my mind is
a bit cluttered with all the learning from yesterday/last night.

I did use the code for the hiding of the password from that link you gave
me. That works excellent. Now I just need to figure out how to refresh the
workbook, and if I remember what VB has to offer it should be
TheWorkBook.refreshall? I am fairly sure that is the command.

There are eight excel sheets in the workbook but I am fairly certain using
the workbook to refresh would be the best idea.

 

[Pflugs]

RefreshAll will work if you have your data set up as External Data, which it
sounds like you do. Also, it is ThisWorkbook, not TheWorkbook.

Good luck. I hope it works out well for you. It sounds like a neat project.
Pflugs

 

[Jessica]

It would be nice for someone to look over the code and tell me if it will or
will not work before I go ahead and commit to it, naturally since it is my
first time doing something like this in excel, I will copy/paste the code
from the sheets to here.

If you would be so kind as to look it over and point out any flaws, that
would be wonderful.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1800 ' Thirty minutes
Public Const CRunWhat = "The_Sub" ' name of procedure to run

Then, on a blank sheet I have called example, there is the toggle button,
named aptly ToggleButton1, with the following code on the sheet.


Private Sub ToggleButton1_Click()
Dim x As String
x = InputBoxDK("Type your password here.", "Password Required", "test")
If x = "test" Then
ToggleButton1.Value = True
End

If ToggleButton1.Value = True Then
'Change caption on Toggle button
'Call StartTimer Sub to begin refreshing
ToggleButton1.Caption = "Refresh Enabled"
StartTimer
Else: ToggleButton1.Value = False
'Change caption on Toggle button
'Stop Timer from running again
ToggleButton1.Caption = "Refresh Disabled"
StopTimer
End If
If x <> "test" Then
MsgBox "You didn't enter the correct password to turn off the refresher."
End If
End If

End Sub

Sub TestDKInputBox()
Dim x As String

x = InputBoxDK("Type your password here.", "Password Required", "test")
If x = "test" Then End
If x <> "yourpassword" Then
MsgBox "You didn't enter a correct password."
End
End If
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=CRunWhat, Schedule:=False

End Sub
Sub The_Sub()
'
'
ThisWorkbook.RefreshAll

StartTimer
End Sub


Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=CRunWhat, Schedule:=True
End Sub

Does that look right? If not, it would be nice to know where I am missing
something, I am testing it as we speak and hopefully in 30 mins it will
refresh.

I do appreciate all the help.

Jessica.

 

[Pflugs]

It looks pretty good. You should define a string variable pword and set it
equal to your password, and then use that variable to do your verification of
user entry. (i.e. If x <> pword...) Also, I see an "else:" halfway down in
your code. That may cause an error. Just use "Else".

Here's my slight revisions to your code.

Private Sub ToggleButton1_Click()
Dim x As String, pword as string
pword = "test"
x = InputBoxDK("Type your password here.", "Password Required", "test")
If x = pword Then
'Change caption on Toggle button
ToggleButton1.Value = True
ToggleButton1.Caption = "Refresh Enabled"

'Call StartTimer Sub to begin refreshing
StartTimer
Else
'Change caption on Toggle button
ToggleButton1.Value = False
ToggleButton1.Caption = "Refresh Disabled"

'Stop Timer from running again
StopTimer

' Notify User
MsgBox "You didn't enter the correct password to turn off the
refresher."
End If

End Sub

Also, rather than waiting 30 minutes to see if your code worked, change it
to 1 minute so you can see it sooner.

HTH,
Pflugs

 

[KLZA]

Are you using some sort of security for your macro code? It seems it
would be easy for your users (if savvy enough) to find the password by
just going into the VB code in your workbook....

 

[Jessica]

Well, after testing it for a while and trying to figure out what is wrong, I
have to errors that have come up.

First one is when you click the toggle button it asks for the password two
times, and then tries to start two timers.

After the minute is up it says "Scheduletest.xls\The_Sub can not be found."

So... any idea?

Jessica

 

[Jessica]

Ok, so I've been working on this for the past hourish, and I have modified my
code a bit, I shall paste it here and someone let me know what is wrong with
it. Currently the StartTimer Sub isn't being called correctly, although the
password box works perfect.



Private Sub ToggleButton1_Click()
Dim x As String, pword As String
pword = "test"
x = InputBoxDK("Type your password here.", "Password Required", "")


'If x = pword Then
'Change caption on Toggle button
' ToggleButton1.Value = True


'Call StartTimer Sub to begin refreshing
' StartTimer
'Else
'Change caption on Toggle button
'ToggleButton1.Value = False
'ToggleButton1.Caption = "Refresh Disabled"

'Stop Timer from running again
' StopTimer

' Notify User
'MsgBox "You didn't enter the correct password to turn off the
refresher."
'End If

End Sub
Sub DKInputBox()

x = InputBoxDK("Type your password here.", "Password Required", "test")
If x = pword Then
ToggleButton1.Value = False
ToggleButton1.Caption = "Refresh Disabled"
StopTimer
Else
ToggleButton1.Value = True
ToggleButton1.Caption = "Refresh Enabled"
MsgBox "You didn't enter a correct password."
StartTimer
End If
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=CRunWhat, Schedule:=False

End Sub
Sub Refresher()
'
'
ThisWorkbook.RefreshAll

StartTimer
End Sub


Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=CRunWhat, Schedule:=True
End Sub

Oh yes, in answer to that comment about security, I will once this is
finished, not that anyone here besides myself knows anything about VBA and
where to find it in Excel. To be honest no one here thought this was even
feasible and if I wouldn't have had Basic (10 (command) \nline 20 (command))
I wouldn't have thought so either.

Thanks for the help.

Jessica.

 

[Pflugs]

Aside from the fact that half of the code is commented, I can't see what
would prevent this code from running coreectly. I believe the sub DKInputBox
is a test sub for the password box and has no impact on the final product,
right?

The only questionable thing I see could be the CRunWhat in the StartTimer
sub. Have you defined what CRunWhat is?

Public Const cRunWhat = "Refresher" ' the name of the procedure to run

Other than that, I'm not sure. If you still have problems with it, I can
pass along my email address for you to email me a copy.

Pflugs

 

[kounoike]

I'm not sure this is what you are looking for.

I modified your code and this one start adding one to a value in A1 every
second when refresh enabled by pushing toggle button and stop adding when
refresh disabled.(instead refresh)

put this in worksheet module where ToggleButtoun reside.

Private Sub ToggleButton1_Click()
Dim x As String
Static out As Boolean
If Not out Then
x = InputBoxDK("Type your password here.", "Password Required",
"test")
Else
out = False
Exit Sub
End If
If x <> "test" Then
out = True
MsgBox "You didn't enter the correct password to turn off the
refresher."
If ToggleButton1.Value = True Then
ToggleButton1.Value = False
Else
ToggleButton1.Value = True
End If
ElseIf x = "test" Then
If ToggleButton1.Value = True Then
ToggleButton1.Caption = "Refresh Enabled"
StartTimer
Else
ToggleButton1.Caption = "Refresh Disabled"
StopTimer
End If
End If
End Sub

and put these in, e.g. module1

Public RunWhen As Double
Public Const cRunIntervalSeconds = 1 ' a second
Public Const CRunWhat = "The_Sub" ' name of procedure to run

Sub The_Sub()
If IsNumeric(Cells(1, 1)) Then
Cells(1, 1).Value = Cells(1, 1).Value + 1
Else
Cells(1, 1) = ""
End If
StartTimer
End Sub

Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=CRunWhat,
Schedule:=True
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=CRunWhat,
Schedule:=False
End Sub

keizi

 

[Jessica]

Oh my, thank you for your help. It works exactly as I thought it should.

I didn't realize I needed to put the other subs in the module public side
instead of on the particular sheet that is acting as the togglebutton control.

Thank you very much.

 

[Pflugs]

I'm sorry, Jessica. I should have made that clearer. Congratulations on
getting the program to work.

Don't forget to take steps to secure the password. (i.e. password protect
the VBA Project, read-only file, etc.)

Pflugs