PDA

View Full Version : Sleeper: VBA assistance



steve.horvat
12-17-2014, 02:57 PM
Hi Everyone,

I have been creating a spreadsheet to try and automate a job creation process and have managed to fumble my way through a few bits.
Below is the code I am using but would like to include a couple of things and don't know how to go about it.

The sheet is saved as a protected XLTM and I also need to maintain the password.

When the control button is clicked a message pops up asking if the file is saved. What I would like it to do is to automatically use the information from 2 cells to be used as the file name and to ensure it is saved as an XLSM.
I would then like it go go to a specific folder in which are various other folders where the user will select the apropriate one.


Much appreciated if anypne can help

Sub Sales_Sending()

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
If ActiveWorkbook.Path <> "" Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

iReply = MsgBox("Have you checked the details and saved this check list?", vbYesNo, " ")
If iReply = vbNo Then ThisWorkbook.Save

strbody = "<font size=""3"" face=""Calibri"">" & _
"Hi Service Coordinator,<br><br><B>" & _
ActiveWorkbook.Name & "</B> has been created and is ready for the next stage in the process.<br>" & _
"<br><br> " & _
"The file can be opened by cliking on the following link : " & _
"<A HREF=""file://" & ActiveWorkbook.FullName & _
""">Link to the file</A>" & _
"<br><br>The full quote folder can be found at " & _
"<A HREF=""file://" & Application.ActiveWorkbook.Path & _
""">Link to the file</A>" & _
"<br><br> "


On Error Resume Next
With OutMail
.Display
.To = "email 1; email 2"
.CC = ""
.BCC = ""
.Subject = " A NEW PROJECT CHECK LIST has been prepared for " & ActiveSheet.Range("D2").Value
.HTMLBody = strbody & "<br>" & .HTMLBody
.Display 'or use .Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "The ActiveWorkbook does not have a path, Save the file first."
End If
End Sub

Bob Phillips
12-17-2014, 03:52 PM
If you are opening a template, how can the path be anything other than empty?

GTO
12-17-2014, 04:05 PM
Greetings Steve,

I presume the above code is in the Macro-Enabled Template; correct?

When you say 'Protected', in what way? Protected from opening and/or modifying (i.e. Read-Only) or once open (or a new unsaved workbook is created using your template as the workbook's template), a sheet or sheets is/are protected and/or workbook protection is enforced for windows and/or structure?

Mark

:hi: Hi Bob :yes

steve.horvat
12-17-2014, 04:55 PM
Correct, saved as Macro-Enabled Template and is protected.
Protection is nothing major just used so a user uses the Tab to go to the next required cell and skip the ones not required. Also to restrict others from making changes to the template

SamT
12-17-2014, 06:32 PM
Correct. All six above. :banghead:

@ Mark. :rotflmao:

GTO
12-17-2014, 09:09 PM
Correct, saved as Macro-Enabled Template and is protected.

Protection is nothing major just used so a user uses the Tab to go to the next required cell and skip the ones not required.

Also to restrict others from making changes to the template


:stars: Well, I think Sam puts it best - and maybe I don't need to be aware of what type of protection(s) are in play - but let me try once more...

Correct, saved as Macro-Enabled Template and is protected.



Yes, I understand it is an .xltm. It's what type or types of protection are in play?


Protection is nothing major just used so a user uses the Tab to go to the next required cell and skip the ones not required.



This sounds as if you are hinting that the worksheet is protected. I am presuming "...uses the Tab..." means uses the Tab key.


Also to restrict others from making changes to the template


:stars:Huh? I'm back to wondering if we are going to hit a password dialog.:what: That said, I did just learn that I could require a password to OPEN the template, but the NEW command doesn't kick up the password requirement, so that was nice to learn.


Anyways, I am still not following how we'd use a cell or two in the template to set the new filename. After you created the first NEW wb based on the template, wouldn't we be trying to use the same filename the next a new wb was created?

This is probably not well thought through, but here is a stab:

In a wb with a sheet with CodeName of 'Sheet1', where Sheet1 contains an ActiveX command button named 'CommandButton1' and the workbook contains a Standard Module named 'Module1':

In the ThisWorkbook module:

Option Explicit

Private Sub Workbook_AfterSave(ByVal Success As Boolean)

Sheet1.CommandButton1.Enabled = ThisWorkbook.Saved

End Sub

Private Sub Workbook_Open()

If Not CBool(Len(ThisWorkbook.Path)) Then

Sheet1.CommandButton1.Enabled = False
ThisWorkbook.Saved = True

If MsgBox("This new workbook needs saved before you can use the code. " & _
"Would you like to save it now?", _
vbYesNo Or vbQuestion, _
vbNullString) = vbYes Then

Application.Dialogs(xlDialogSaveAs).Show "My_Report_" & Format(Now, "yyyymmdd hhmm"), 52

End If

End If

End Sub

I am not sure if the AfterSave event is in 2007, I have 2010.

In Sheet1's Object Module:


Option Explicit
Private Sub CommandButton1_Click()
Module1.Sales_Sending
End Sub


In Module1:


Option Explicit

Sub Sales_Sending()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Dim iReply As Long

If Not CBool(Len(ThisWorkbook.Path)) Then

If MsgBox("This new workbook needs saved before you can use the code. " & _
"Would you like to save it now?", _
vbYesNo Or vbQuestion, _
vbNullString) = vbYes Then

If Not Application.Dialogs(xlDialogSaveAs).Show("My_Report_" & Format(Now, "yyyymmdd hhmm"), 52) = -1 Then
MsgBox "I cannot email the wb until saved.", vbInformation, vbNullString
Exit Sub
End If

End If

Else
If Not ThisWorkbook.Saved Then
If MsgBox("I need to save before sending by email; is that okay?", _
vbYesNo Or vbQuestion, _
"Save Required...") = vbYes Then

ThisWorkbook.Save

End If
End If
End If

If Not ThisWorkbook.Saved Then
Sheet1.CommandButton1.Enabled = False
Exit Sub
Else
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "<font size=""3"" face=""Calibri"">Hi Service Coordinator,<br><br><B>" & _
ActiveWorkbook.Name & _
"</B> has been created and is ready for the next stage in the process.<br><br><br> " & _
"The file can be opened by cliking on the following link : <A HREF=""file://" & _
ActiveWorkbook.FullName & _
""">Link to the file</A><br><br>" & _
"The full quote folder can be found at <A HREF=""file://" & _
Application.ActiveWorkbook.Path & _
""">Link to the file</A><br><br> "

On Error Resume Next
With OutMail
.Display
.To = "Someone@somewhere.zit"
.CC = ""
.BCC = ""
.Subject = " A NEW PROJECT CHECK LIST has been prepared for " & ActiveSheet.Range("D2").Value
.HTMLBody = strbody & "<br>" & .HTMLBody
'.Display 'or use .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End If

End Sub

Does that help?

Mark

SamT
12-18-2014, 04:18 AM
This is when he comes back and says, "that's not what I meant." :D

I have never seen the AfterSave Event before, but wouldn't
Sheet1.CommandButton1.Enabled = Success do the same thing?

That trick with Path String length is quite proper and will never fail you. :bow: I like your style

I prefer the obvious over the proper (when it works) so, in VBA for Excel, I would use
If ThisWorkbook.Path = "" Then

I've got this thing about keeping all the event subs in ThisWorkbook One-Liners so I would move the code in the Open Event sub to Module1 as Sub1 and use
Private Sub Workbook_Open()
If Not CBool(Len(ThisWorkbook.Path)) Then Module1.Sub1
End Sub

GTO
12-18-2014, 05:23 AM
This is when he comes back and says, "that's not what I meant." :D

Sigh... I am confident you are quite correct.


I have never seen the AfterSave Event before, but wouldn't
Sheet1.CommandButton1.Enabled = Success do the same thing?

Yes (presumably). Unfortunately, I do not get to code often and have learned so much less than I'd like over the past couple of years. Thus, I tend to use what I "know" works.

I think you have 2002, so attached is the help topic in 2010 (which, and whining... I like so much less than the help in 2000).


That trick with Path String length is quite proper and will never fail you. :bow: I like your style

I prefer the obvious over the proper (when it works) so, in VBA for Excel, I would use
If ThisWorkbook.Path = "" Then

:blush Thank you Sam.

I personally would only change the "" to vbNullString.

I really wasn't trying to 'overly trick' it up; but I had tried DIR first and found that it returned a LEN of one, a dot!! Presumably DOS related (?) root or whatever has fallen out of my brain, but suddenly I became rather specific in the return...



I've got this thing about keeping all the event subs in ThisWorkbook One-Liners so I would move the code in the Open Event sub to Module1 as Sub1 and use
Private Sub Workbook_Open()
If Not CBool(Len(ThisWorkbook.Path)) Then Module1.Sub1
End Sub

I do not agree unconditionally, but that makes sense. For me, or I should say, for my eyes (whatever one finds quickest to read/absorb) - if it's reasonably short and specific to the WB, then I stick the code in the event procedure.

During a recent thread, we touched on making the wb obvious in needing macros enabled (and my code went more to making the wb useless in not enabled). In the several times I've done that at work (many users), I finally grasped one way of making it easier (portable) in that I put most all of the code in the macro warning sheet's module, and call those procedures, as you say, with one-liners in ThisWorkbook. (I still use the BeforeSave to "build" a pseudo AfterSave, but will get around to correcting that at some point...)

Anyways, I certainly see the wisdom in your preference:thumb.

Mark

GTO
12-18-2014, 05:27 AM
12628Sorry... blond... No more than hit Submit and realized I failed to include the attachment...

SamT
12-18-2014, 06:48 AM
At least you have a help topic. I understand that 2007 Users have to use the internet