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 here:
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, 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.

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

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....

On Aug 15, 3:42 pm, Pflugs <Pfl...@discussions.microsoft.com> wrote:
> 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, 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.- Hide quoted text -
>
> - Show quoted text -

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

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.

Good afternoon,

I do apologize for the later reply, but I just came to work. :-)

While I was test running the langauge, again, the password box is fine, yes.
It is when it is trying to call the timer to start that it doesn't actually
start.

To clarify, when the spreadsheet opens, it never refreshes, even with the
togglebutton1 value being true. Now when I push the button it does ask for
the password and I'd assume it stops the timer since the button action is
false. When I repush the button to start the refresh, it doesn't start the
timer and I haven't figured out what is needed to start the timer from the
code that is there.

I wish I could email you the example, but our email is heavily restricted to
only people inside the company (I am not IT persay, I'm a floor person doing
this work as requested).

Any help is definately appreciated. :-)

Jessica
P.S. The commented parts are what I removed just to see if the code would
work with them gone, granted it hasn't worked 100% yet, but the error to call
the sub StartTimer is no longer popping up.