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

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 he
http://www.oaltd.co.uk/Indenter/IndentPage.asp

HTH,
Pflugs

"Jessica" wrote:

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

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.

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" wrote:

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.

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, Procedu=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, Procedu=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.

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

"Jessica" wrote:

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, Procedu=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, Procedu=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.