Problems with "Retry" after Incorrect Password

CravingGod

New Member
Joined
Dec 31, 2014
Messages
7
I am new to VBA and have made many errors and learned from a lot of them. I have made a Userform that has some code copied and pasted from various sources. So far the only hiccup I haven't gotten past is figuring out how to restart the whole Userform code if the Retry button is clicked.


Below is the code I have pieced together. I have put the text in bold where I think my problem is. Any help is appreciated.


Private Sub CommandButton1_Click()
1 If TextBox1.Value = "Password" Then
'code for dilague box
rspn = MsgBox("Are you sure you want to email every statement?", vbYesNo)
If rspn = vbNo Then Unload Me

'code'For Tips see: (website)
'Don't forget to copy the function RangetoHTML in the module.
'Working in Excel 2000-2013
Dim OutApp As Object
Dim OutMail As Object
Dim ws As Worksheet

With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set OutApp = CreateObject("Outlook.Application")

For Each ws In ActiveWorkbook.Worksheets
If ws.Range("C17").Value Like "?*@?*.?*" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = ws.Range("C17").Value
.CC = ""
.BCC = ""
.Subject = "Your Statement is Ready"
.HTMLBody = RangetoHTML(ws.UsedRange)
'You can add a file like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
End If
Next ws
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With

Else
OutPut = MsgBox("Please enter the correct password to Email Satatements?", vbRetryCancel + vbDefaultButton2, "Oops")
If OutPut = 2 Then Unload Me
If OutPut = 4 Then


End If
Unload Me
End Sub
Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2013
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.ReadAll
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close savechanges:=False

'Delete the htm file we used in this function
Kill TempFile

Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function

Private Sub CommandButton2_Click()
Unload Me
End Sub
 

par60056

Well-known Member
Joined
Jul 26, 2012
Messages
1,571
welcome to the forum.

Use of the "[ c o d e ]" and "[ / c o d e ]" tags will make your posts more readable.

If I understand what you are doing, you could rewrite the logic so that it loops until they get it write or stop trying, or you could add "call CommandButton1_Click()" to recurse and call the procedure again. The problem with recursing is if they retry too many times it will error.
 

Forum statistics

Threads
1,082,287
Messages
5,364,312
Members
400,789
Latest member
Gnar

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top