Hi guys, I hope you are doing well.
Would you please assist with the below.
I’m attaching after save event to close.
However, I want to proceed with an update to ask the code to close the sheet as read-only mode.
What kind of modification I should apply here?
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
ThisWorkbook.Close
End If
End SubYeah sure, here you go
This post was originally put in MrExcel forum, but I haven't got single reply so 
Here's the original post link:
https://www.mrexcel.com/forum/…ing-after-save-event.html
Hi Guys, would you please help.
I'd this workbook event before and it was working just fine
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
ThisWorkbook.Close
End If
End SubHowever, I needed to proceed with some changes for the event, yet, it doesn't work for closing part anymore:
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim i As Long
i = ActiveCell.Colum
Cells(Range("A65536").End(xlUp).Row + 1, 1).Select
ActiveCell.Resize(2).EntireRow.Copy Sheets("Report").Range("A" & Rows.Count).End(xlUp).Offset(1)
ActiveCell.Offset(0, 11).Value = Format(Now(), "dd/mm/yyyy")
ActiveCell.Offset(0, 12).Value = Format(Now(), "hh:nn:ss")
If Success Then
ThisWorkbook.Close
End If
End SubHow I could sort this out?
Another point, I'd like to add part for this event to run the first part only if the active sheet name is one of the available names in Worksheets("DB").Range("A1:A100"), otherwise, to just save and close, is it doable?
Thank you guys, it has been solved by saving this event in the workbook macro
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
If Success Then
ThisWorkbook.Close
End If
End SubGuys, why this code doesn’t work when saving by pressing ctrl+s although it works when debugging the code?
Sub Auto_Close()
If ThisWorkbook.Saved = True Then
ThisWorkbook.Close
End If
End SubHi guys, I hope you’re doing great.
Quick question for you, I’m trying to hyperlink file with extension of ppsx in Excel.
You got it right, ppsx it’s PowerPoint slide show that’s supposed to open in full screen, show the slides by each click, and the whole programme window is supposed to be closed once the show is over.
However, whenever you hyperlink it in any other file, like Word, or Excel like what I need here, it doesn’t behave that way anymore, it opens the slide show but not in full screen, and it doesn’t auto-close the PowerPoint window after finishing the show.
It was expect to do that at least. I don’t know why it acts that way and how to overcome this problem, I began to hate PowerPoint, the simplest tasks it doesn’t do it smoothly
I don’t know how to show the error as the excel simply contains hyperlink that is linked locally to a file in my C-Drive
Anyways I'm attaching the excel file but I'm afraid that the forum website doesn't allow uploading PPSX format
Well look, it seems that it was totally my bad, I truly apologize to you
The thing is, that I was adding pre-defined values to my ComboBox available for everyone via the shame itself.
When I tried the code, I missed to remove these values, that’s why I was getting error.
It seems that my code was just fine, I haven’t tried it yet, but logically the same mistake might be the reason that it didn’t work with me from the first place.
I’ll test it later anyways and provide a feedback to leave the post useful for whoever will follow it later on.
And again, thank you so much for your prompt assistance, you’re very helpful.
Yeah, I understand
It seems that this happened while I'm typing here only
But the code was pasted correctly in my sheet as per the below screenshow[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"123456.PNG","data-attachmentid":1202109}[/ATTACH]
Simply, it asks me to Debug or End
The problem happens starting from this line anyways if this will help to rectify the issue
'Set Variable MyArr = Array("X2", "X3", "X4", "X5", "X6", "X7")I'm afraid it didn't work neither
I save it in the code of the sheet where the ComboBox exists
Hi Guys,
I hope your day goes perfectly.
I wonder if anyone can tell what is wrong with this code?
I’m trying to use the login username to control what options would be given through some ComboBox
However, it seems that it returns with an error:
Sub XControlEditing()
If Environ$("UserName") = "magdoulinshamseldin" Then
Me.ComboBox3.List = Array("X1")
Else
Me.ComboBox3.List = Array("X2", "X3", "X4", "X5", "X6", "X7")
End If
End SubKindly note that this code is saved in the sheet where the ComboBox exists, not in module, however, I’ve tried to put it in separated module, it didn’t work neither
I’ve tried function with as well in If statement, and it didn’t work neither, thank you.
The other topin in the other forum has been closed, yet, here is its link:
https://www.mrexcel.com/forum/…ssage-body-word-file.html
Hi Guys,
I’m facing an issue
That the message body is generated filtered from any formatting that was there in the original word file
I need to keep the formatting
Moreover
The word file contains table, I need to keep the table format as it is there in the word file
What is happening now that it transfers it to String
I know my code states that MsgTxt variable declared as String
However, I don’t know what would be the right alternative to use
Please advise me with these two points
PS there was two typical topics for the same purposes here and they were closed, the links are as following:
https://www.ozgrid.com/forum/forum/h...from-word-file
https://www.ozgrid.com/forum/forum/h...from-word-file
The post was published in another forum, however it has been closed by now, here is the link:
https://www.mrexcel.com/forum/excel-...word-file.html
The code now is as following
Sub TC_Template1()
Dim oMailItem As Object
Dim oOLapp As Object
Dim Word As Object
Dim doc As Object
Dim MsgTxt As String
Set Word = CreateObject("word.application")
Set doc = Word.Documents.Open _
(Filename:=" C:\Users\RMA2\Desktop\For Paul\Missing Course Certificate.docx", ReadOnly:=True)
'Pulls text from file for message body
MsgTxt = doc.Range(Start:=doc.Paragraphs(1).Range.Start, _
End:=doc.Paragraphs(doc.Paragraphs.Count).Range.End)
Set oOLapp = CreateObject("Outlook.Application")
Set oMailItem = oOLapp.CreateItem(0)
With oMailItem
.To = "Email"
.CC = "Email”
.Body = MsgTxt
.Subject = "Missing Course Certificate"
.Display
End With
Set oOLapp = Nothing
Set oMailItem = Nothing
End SubGuys, I guess I figured out what was wrong
It was a matter of instructions order
However, I’m still facing another issue
That the message body is generated filtered from any formatting that was there in the original word file
I need to keep the formatting
Moreover
The word file contains table, I need to keep the table format as it is there
What is happening now that it transfers it to String
I know my code states that MsgTxt variable declared as String
However, I don’t know what would be the right alternative to use
Please advise me with these two points
The code now is as following
Sub TC_Template1()
Dim oMailItem As Object
Dim oOLapp As Object
Dim Word As Object
Dim doc As Object
Dim MsgTxt As String
Set Word = CreateObject("word.application")
Set doc = Word.Documents.Open _
(Filename:=" C:\Users\RMA2\Desktop\For Paul\Missing Course Certificate.docx", ReadOnly:=True)
'Pulls text from file for message body
MsgTxt = doc.Range(Start:=doc.Paragraphs(1).Range.Start, _
End:=doc.Paragraphs(doc.Paragraphs.Count).Range.End)
Set oOLapp = CreateObject("Outlook.Application")
Set oMailItem = oOLapp.CreateItem(0)
With oMailItem
.To = "Email"
.CC = "Email”
.Body = MsgTxt
.Subject = "Missing Course Certificate"
.Display
End With
Set oOLapp = Nothing
Set oMailItem = Nothing
End SubHi Guys,
I am trying to write an VBA Code that would help creating Outlook Email message with To, CC and Subject fields.
However, the body, I need to be read from Word File saved in my computer drive.
Here is what I have reached so far, yet, it works only for creating the email message with the needed field except for the body part.
P.S. the similar topic on the other forum has been closed, please keep this one then.
Sub Email_Template1()
Dim oMailItem As Object
Dim oOLapp As Object
Dim Word As Object
Dim doc As Object
Dim MsgTxt As String
Set oOLapp = CreateObject("Outlook.Application")
Set oMailItem = oOLapp.CreateItem(0)
With oMailItem
.To = "Email"
.CC = "Email"
.Subject = "Needed Confirmation"
.Display
End With
Set Word = CreateObject("word.application")
Set doc = Word.Documents.Open _
(FileName:="G:\Customer Services\File Plan\Magdoulin\To be deleted 1.docx", ReadOnly:=True)
'Pulls text from file for message body
MsgTxt = doc.Range(Start:=doc.Paragraphs(1).Range.Start, _
End:=doc.Paragraphs(doc.Paragraphs.Count).Range.End)
Set oOLapp = Nothing
Set oMailItem = Nothing
End SubHi Guys,
I am trying to write an VBA Code that would help creating Outlook Email message with To, CC and Subject fields.
However, the body, I need to be read from Word File saved in my computer drive.
Here is what I have reached so far, yet, it works only for creating the email message with the needed field except for the body part.
Sub Email_Template1()
Dim oMailItem As Object
Dim oOLapp As Object
Dim Word As Object
Dim doc As Object
Dim MsgTxt As String
Set oOLapp = CreateObject("Outlook.Application")
Set oMailItem = oOLapp.CreateItem(0)
With oMailItem
.To = "Email"
.CC = "Email"
.Subject = "Needed Confirmation"
.Display
End With
Set Word = CreateObject("word.application")
Set doc = Word.Documents.Open _
(FileName:="G:\Customer Services\File Plan\Magdoulin\To be deleted 1.docx", ReadOnly:=True)
'Pulls text from file for message body
MsgTxt = doc.Range(Start:=doc.Paragraphs(1).Range.Start, _
End:=doc.Paragraphs(doc.Paragraphs.Count).Range.End)
Set oOLapp = Nothing
Set oMailItem = Nothing
End SubAh, that was the issue then
Alright
Sir, you got it all sorted, thank you so much
Really appreciate it
Well first of all, let me salut you, you're such an elegant person, your sheets speak loudly and says so
Well, now there's a little issue, it seems that this method doesn't let the Combo Box show all the elements which are supposed to be there
As I confirmed, the attached is just a prototype, the original sheet is quite different, the combo boxes number of elements isn't identical all the time
I've adjusted your sheet with my original sheet elements, in the "Lists" sheet, as you could see the Combo Boxes don't work properly anymore
Could I ask for your kind assistance here? What's wrong I'm doing?
Well, this sheet is way simplified, so maybe the purpose of the last combo box isn’t that clear
However, the original one isn’t like that 100%
It’s way complicated and the options aren’t unified with the 4th combo box
I need just the technique
Well, what about trying to replace the whole thing with Combo Boxes?
I’ve attached sample sheet which is prototype of my original sheet
As you should see, I need the 1st Combo Box to include the options of X/Y/Z
And the 2nd one to include A/B/C or D/E/F or G/H/I, depends on the 1st selection
And the 3rd & 4th Combo Boxes to work the same way