Editing a simple macro

[Connie Martin]

I have this simple macro, which works fine:

Sub CopyFormDM()
'
' CopyFormDM Macro
' Macro recorded 11/25/2005 by Connie Martin
'

'
ActiveWindow.LargeScroll ToRight:=4
Range("HH12:IO19").Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-4
ActiveWindow.SmallScroll Down:=9
Range("X20:AG21").Select
End Sub

I would like to password protect the form and edit the macro so that part of
the macro is to unprotect the form first, run the rest of the macro and then
password protect it again before ending. When I recorded the macro that way
and then later ran it, it stopped for me to put in the password, and then
ended by protecting it without a password. I would like to edit the macro so
that it puts the password in to unprotect and puts it in again to protect. I
realize anyone looking at the macro in VB will see the password but this file
is to be used by people who most likely don't know how to do that, and if
they did, it would be no big harm done.

Thank you
Connie Martin

[Gord Dibben]

ActiveSheet.Unprotect Password:="justme"

ActiveWindow.LargeScroll ToRight:=4
Range("HH12:IO19").Select
Selection.Copy
ActiveWindow.LargeScroll ToRight:=-4
ActiveWindow.SmallScroll Down:=9
Range("X20:AG21").Select

ActiveSheet.Protect Password:="justme"


Gord Dibben Excel MVP

On Fri, 25 Nov 2005 11:51:40 -0800, "Connie Martin"
<[email protected]> wrote:

>I have this simple macro, which works fine:
>
>Sub CopyFormDM()
>'
>' CopyFormDM Macro
>' Macro recorded 11/25/2005 by Connie Martin
>'
>
>'
> ActiveWindow.LargeScroll ToRight:=4
> Range("HH12:IO19").Select
> Selection.Copy
> ActiveWindow.LargeScroll ToRight:=-4
> ActiveWindow.SmallScroll Down:=9
> Range("X20:AG21").Select
>End Sub
>
>I would like to password protect the form and edit the macro so that part of
>the macro is to unprotect the form first, run the rest of the macro and then
>password protect it again before ending. When I recorded the macro that way
>and then later ran it, it stopped for me to put in the password, and then
>ended by protecting it without a password. I would like to edit the macro so
>that it puts the password in to unprotect and puts it in again to protect. I
>realize anyone looking at the macro in VB will see the password but this file
>is to be used by people who most likely don't know how to do that, and if
>they did, it would be no big harm done.
>
>Thank you
>Connie Martin

[Connie Martin]

Okay, great. That works. Although, dumb me, I just discovered I don't need
to unprotect the worksheet to copy cells! I was trying to do several things
with this macro and some of it I changed because no one was able to answer my
previous question "Recording Macro". I would you like to record typing text
in a cell, then copying it, then deleting it, password protecting the file
again, and ending. But I lose what's on the clipboard. There appears to be
no way to keep what I copied.

So, it would be like this:

ActiveSheet.Unprotect Password:="justme"

Range("B13:AG13").Select
ActiveCell.FormulaR1C1 = _
"This form is sent to you for your approval for special expediting."
& Chr(10) & "Please reply to sender and copy Valerie Hill and Connie Martin."
With ActiveCell.Characters(Start:=1, Length:=130).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A13:AH20").Select
Selection.Copy
Range("B13:AG13").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("X21:AG22").Select
End Sub

ActiveSheet.Protect Password:="justme"

Is there any way to retain the copied text on the clipboard? Connie Martin

"Gord Dibben" wrote:

> ActiveSheet.Unprotect Password:="justme"
>
> ActiveWindow.LargeScroll ToRight:=4
> Range("HH12:IO19").Select
> Selection.Copy
> ActiveWindow.LargeScroll ToRight:=-4
> ActiveWindow.SmallScroll Down:=9
> Range("X20:AG21").Select
>
> ActiveSheet.Protect Password:="justme"
>
>
> Gord Dibben Excel MVP
>
> On Fri, 25 Nov 2005 11:51:40 -0800, "Connie Martin"
> <[email protected]> wrote:
>
> >I have this simple macro, which works fine:
> >
> >Sub CopyFormDM()
> >'
> >' CopyFormDM Macro
> >' Macro recorded 11/25/2005 by Connie Martin
> >'
> >
> >'
> > ActiveWindow.LargeScroll ToRight:=4
> > Range("HH12:IO19").Select
> > Selection.Copy
> > ActiveWindow.LargeScroll ToRight:=-4
> > ActiveWindow.SmallScroll Down:=9
> > Range("X20:AG21").Select
> >End Sub
> >
> >I would like to password protect the form and edit the macro so that part of
> >the macro is to unprotect the form first, run the rest of the macro and then
> >password protect it again before ending. When I recorded the macro that way
> >and then later ran it, it stopped for me to put in the password, and then
> >ended by protecting it without a password. I would like to edit the macro so
> >that it puts the password in to unprotect and puts it in again to protect. I
> >realize anyone looking at the macro in VB will see the password but this file
> >is to be used by people who most likely don't know how to do that, and if
> >they did, it would be no big harm done.
> >
> >Thank you
> >Connie Martin
>
>

[Gord Dibben]

Connie

I see what you're doing with this macro but I don't understand why.

See comments in line

On Fri, 25 Nov 2005 12:58:01 -0800, "Connie Martin"
<[email protected]> wrote:

>Okay, great. That works. Although, dumb me, I just discovered I don't need
>to unprotect the worksheet to copy cells! I was trying to do several things
>with this macro and some of it I changed because no one was able to answer my
>previous question "Recording Macro". I would you like to record typing text
>in a cell, then copying it, then deleting it, password protecting the file
>again, and ending. But I lose what's on the clipboard. There appears to be
>no way to keep what I copied.
>
>So, it would be like this:
>
>ActiveSheet.Unprotect Password:="justme"
>
>Range("B13:AG13").Select
> ActiveCell.FormulaR1C1 = _
> "This form is sent to you for your approval for special expediting."
>& Chr(10) & "Please reply to sender and copy Valerie Hill and Connie Martin."
> With ActiveCell.Characters(Start:=1, Length:=130).Font
> .Name = "Arial"
> .FontStyle = "Bold"
> .Size = 14
> .Strikethrough = False
> .Superscript = False
> .Subscript = False
> .OutlineFont = False
> .Shadow = False
> .Underline = xlUnderlineStyleNone
> .ColorIndex = xlAutomatic
> End With
Formatted text is now in B13

> Range("A13:AH20").Select
> Selection.Copy
Copies A13:AH20 which includes the text in B13

> Range("B13:AG13").Select
> Application.CutCopyMode = False.
> Selection.ClearContents
Clears contents from B13:AG13

> Range("X21:AG22").Select
What are you going to do here?

>End Sub
>
>ActiveSheet.Protect Password:="justme"
This line fits in before End Sub, not after.
>
>Is there any way to retain the copied text on the clipboard?
To do what with it?
Edit>Clipboard shows me the copied contents.


Gord


>"Gord Dibben" wrote:
>
>> ActiveSheet.Unprotect Password:="justme"
>>
>> ActiveWindow.LargeScroll ToRight:=4
>> Range("HH12:IO19").Select
>> Selection.Copy
>> ActiveWindow.LargeScroll ToRight:=-4
>> ActiveWindow.SmallScroll Down:=9
>> Range("X20:AG21").Select
>>
>> ActiveSheet.Protect Password:="justme"
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Fri, 25 Nov 2005 11:51:40 -0800, "Connie Martin"
>> <[email protected]> wrote:
>>
>> >I have this simple macro, which works fine:
>> >
>> >Sub CopyFormDM()
>> >'
>> >' CopyFormDM Macro
>> >' Macro recorded 11/25/2005 by Connie Martin
>> >'
>> >
>> >'
>> > ActiveWindow.LargeScroll ToRight:=4
>> > Range("HH12:IO19").Select
>> > Selection.Copy
>> > ActiveWindow.LargeScroll ToRight:=-4
>> > ActiveWindow.SmallScroll Down:=9
>> > Range("X20:AG21").Select
>> >End Sub
>> >
>> >I would like to password protect the form and edit the macro so that part of
>> >the macro is to unprotect the form first, run the rest of the macro and then
>> >password protect it again before ending. When I recorded the macro that way
>> >and then later ran it, it stopped for me to put in the password, and then
>> >ended by protecting it without a password. I would like to edit the macro so
>> >that it puts the password in to unprotect and puts it in again to protect. I
>> >realize anyone looking at the macro in VB will see the password but this file
>> >is to be used by people who most likely don't know how to do that, and if
>> >they did, it would be no big harm done.
>> >
>> >Thank you
>> >Connie Martin
>>
>>

[Connie Martin]

Part of the worksheet is a small form that requires completion which, upon
completion, is to be e-mailed. I automated it as much as I could so that
when they complete the form they simply click on a button that copies just
the small form part within the worksheet, and then they click on a hyperlink
which automatically takes them to their e-mail already addressed, and they
simply paste the form in the e-mail and send it off. The original way I
wanted to do the macro was:
1. unprotect the form (and you showed me how to do that with the password
embedded in the macro)
2. Type an instruction which is to appear in the e-mail with the form in a
row just above the form part of the worksheet
3. Copy that cell together with the form
4. Delete the row with typed instructions
5. Protect the worksheet again

But unfortunately, what was copied to the clipboard doesn't remain there
after step 3, so there's nothing to paste in the e-mail. So, what I did was
do a linked copy of the form in columns far to the right, way out of sight,
with the instructions that are to appear in the e-mail with the form typed in
the row above it, so when the person completes the "real" form at the
beginning of the worksheet, the one to the far right is populated and the
macro copies that one, to paste in the e-mail with the special instruction
which is typed only above the linked form in the far right columns of the
worksheet.

The form still has to remain unprotected because when it's protected and if
the person completing it tabs to the cells for completion, it tabs
automatically way over to the linked copy of the form, and I don't want them
to be aware that it's over there.

I tried hiding the linked columns and recorded the macro unhiding it,
copying it, and hiding it again, but what got pasted in the e-mail was
"hidden columns", therefore nothing.

Sounds complicated, but I'm trying to automate this form to make it as easy
as possible for others to complete. People hate filling out forms but if you
automate it as much as possible, they don't complain so much!

Connie

"Gord Dibben" wrote:

> Connie
>
> I see what you're doing with this macro but I don't understand why.
>
> See comments in line
>
> On Fri, 25 Nov 2005 12:58:01 -0800, "Connie Martin"
> <[email protected]> wrote:
>
> >Okay, great. That works. Although, dumb me, I just discovered I don't need
> >to unprotect the worksheet to copy cells! I was trying to do several things
> >with this macro and some of it I changed because no one was able to answer my
> >previous question "Recording Macro". I would you like to record typing text
> >in a cell, then copying it, then deleting it, password protecting the file
> >again, and ending. But I lose what's on the clipboard. There appears to be
> >no way to keep what I copied.
> >
> >So, it would be like this:
> >
> >ActiveSheet.Unprotect Password:="justme"
> >
> >Range("B13:AG13").Select
> > ActiveCell.FormulaR1C1 = _
> > "This form is sent to you for your approval for special expediting."
> >& Chr(10) & "Please reply to sender and copy Valerie Hill and Connie Martin."
> > With ActiveCell.Characters(Start:=1, Length:=130).Font
> > .Name = "Arial"
> > .FontStyle = "Bold"
> > .Size = 14
> > .Strikethrough = False
> > .Superscript = False
> > .Subscript = False
> > .OutlineFont = False
> > .Shadow = False
> > .Underline = xlUnderlineStyleNone
> > .ColorIndex = xlAutomatic
> > End With
> Formatted text is now in B13
>
> > Range("A13:AH20").Select
> > Selection.Copy
> Copies A13:AH20 which includes the text in B13
>
> > Range("B13:AG13").Select
> > Application.CutCopyMode = False.
> > Selection.ClearContents
> Clears contents from B13:AG13
>
> > Range("X21:AG22").Select
> What are you going to do here?
>
> >End Sub
> >
> >ActiveSheet.Protect Password:="justme"
> This line fits in before End Sub, not after.
> >
> >Is there any way to retain the copied text on the clipboard?
> To do what with it?
> Edit>Clipboard shows me the copied contents.
>
>
> Gord
>
>
> >"Gord Dibben" wrote:
> >
> >> ActiveSheet.Unprotect Password:="justme"
> >>
> >> ActiveWindow.LargeScroll ToRight:=4
> >> Range("HH12:IO19").Select
> >> Selection.Copy
> >> ActiveWindow.LargeScroll ToRight:=-4
> >> ActiveWindow.SmallScroll Down:=9
> >> Range("X20:AG21").Select
> >>
> >> ActiveSheet.Protect Password:="justme"
> >>
> >>
> >> Gord Dibben Excel MVP
> >>
> >> On Fri, 25 Nov 2005 11:51:40 -0800, "Connie Martin"
> >> <[email protected]> wrote:
> >>
> >> >I have this simple macro, which works fine:
> >> >
> >> >Sub CopyFormDM()
> >> >'
> >> >' CopyFormDM Macro
> >> >' Macro recorded 11/25/2005 by Connie Martin
> >> >'
> >> >
> >> >'
> >> > ActiveWindow.LargeScroll ToRight:=4
> >> > Range("HH12:IO19").Select
> >> > Selection.Copy
> >> > ActiveWindow.LargeScroll ToRight:=-4
> >> > ActiveWindow.SmallScroll Down:=9
> >> > Range("X20:AG21").Select
> >> >End Sub
> >> >
> >> >I would like to password protect the form and edit the macro so that part of
> >> >the macro is to unprotect the form first, run the rest of the macro and then
> >> >password protect it again before ending. When I recorded the macro that way
> >> >and then later ran it, it stopped for me to put in the password, and then
> >> >ended by protecting it without a password. I would like to edit the macro so
> >> >that it puts the password in to unprotect and puts it in again to protect. I
> >> >realize anyone looking at the macro in VB will see the password but this file
> >> >is to be used by people who most likely don't know how to do that, and if
> >> >they did, it would be no big harm done.
> >> >
> >> >Thank you
> >> >Connie Martin
> >>
> >>
>
>

[Gord Dibben]

Connie, if you're still out there<g>

I would quit messing about with adding the text then deleting it.

Start a new message in Outlook.

Enter all the to: and cc: addresses etc.

Place your message line at top of the message body.

Format as you wish.

File>Save As>Template(*.oft) file.

In Excel stick this Sub in a general module

Sub senditout()
ActiveSheet.Range("A13:AH20").Copy
ActiveWorkbook.FollowHyperlink Address:="e:\GordStuff\thankyou.oft", _
NewWindow:=True
End Sub

Change the path to your *.oft file

The range will be copied, the message template will be opened, the user can
paste in below your message line and send.

Return to Excel and hit Escape key.


Gord

On Mon, 28 Nov 2005 07:17:05 -0800, "Connie Martin"
<[email protected]> wrote:

>Part of the worksheet is a small form that requires completion which, upon
>completion, is to be e-mailed. I automated it as much as I could so that
>when they complete the form they simply click on a button that copies just
>the small form part within the worksheet, and then they click on a hyperlink
>which automatically takes them to their e-mail already addressed, and they
>simply paste the form in the e-mail and send it off. The original way I
>wanted to do the macro was:
>1. unprotect the form (and you showed me how to do that with the password
>embedded in the macro)
>2. Type an instruction which is to appear in the e-mail with the form in a
>row just above the form part of the worksheet
>3. Copy that cell together with the form
>4. Delete the row with typed instructions
>5. Protect the worksheet again
>
>But unfortunately, what was copied to the clipboard doesn't remain there
>after step 3, so there's nothing to paste in the e-mail. So, what I did was
>do a linked copy of the form in columns far to the right, way out of sight,
>with the instructions that are to appear in the e-mail with the form typed in
>the row above it, so when the person completes the "real" form at the
>beginning of the worksheet, the one to the far right is populated and the
>macro copies that one, to paste in the e-mail with the special instruction
>which is typed only above the linked form in the far right columns of the
>worksheet.
>
>The form still has to remain unprotected because when it's protected and if
>the person completing it tabs to the cells for completion, it tabs
>automatically way over to the linked copy of the form, and I don't want them
>to be aware that it's over there.
>
>I tried hiding the linked columns and recorded the macro unhiding it,
>copying it, and hiding it again, but what got pasted in the e-mail was
>"hidden columns", therefore nothing.
>
>Sounds complicated, but I'm trying to automate this form to make it as easy
>as possible for others to complete. People hate filling out forms but if you
>automate it as much as possible, they don't complain so much!
>
>Connie
>
>"Gord Dibben" wrote:
>
>> Connie
>>
>> I see what you're doing with this macro but I don't understand why.
>>
>> See comments in line
>>
>> On Fri, 25 Nov 2005 12:58:01 -0800, "Connie Martin"
>> <[email protected]> wrote:
>>
>> >Okay, great. That works. Although, dumb me, I just discovered I don't need
>> >to unprotect the worksheet to copy cells! I was trying to do several things
>> >with this macro and some of it I changed because no one was able to answer my
>> >previous question "Recording Macro". I would you like to record typing text
>> >in a cell, then copying it, then deleting it, password protecting the file
>> >again, and ending. But I lose what's on the clipboard. There appears to be
>> >no way to keep what I copied.
>> >
>> >So, it would be like this:
>> >
>> >ActiveSheet.Unprotect Password:="justme"
>> >
>> >Range("B13:AG13").Select
>> > ActiveCell.FormulaR1C1 = _
>> > "This form is sent to you for your approval for special expediting."
>> >& Chr(10) & "Please reply to sender and copy Valerie Hill and Connie Martin."
>> > With ActiveCell.Characters(Start:=1, Length:=130).Font
>> > .Name = "Arial"
>> > .FontStyle = "Bold"
>> > .Size = 14
>> > .Strikethrough = False
>> > .Superscript = False
>> > .Subscript = False
>> > .OutlineFont = False
>> > .Shadow = False
>> > .Underline = xlUnderlineStyleNone
>> > .ColorIndex = xlAutomatic
>> > End With
>> Formatted text is now in B13
>>
>> > Range("A13:AH20").Select
>> > Selection.Copy
>> Copies A13:AH20 which includes the text in B13
>>
>> > Range("B13:AG13").Select
>> > Application.CutCopyMode = False.
>> > Selection.ClearContents
>> Clears contents from B13:AG13
>>
>> > Range("X21:AG22").Select
>> What are you going to do here?
>>
>> >End Sub
>> >
>> >ActiveSheet.Protect Password:="justme"
>> This line fits in before End Sub, not after.
>> >
>> >Is there any way to retain the copied text on the clipboard?
>> To do what with it?
>> Edit>Clipboard shows me the copied contents.
>>
>>
>> Gord
>>
>>
>> >"Gord Dibben" wrote:
>> >
>> >> ActiveSheet.Unprotect Password:="justme"
>> >>
>> >> ActiveWindow.LargeScroll ToRight:=4
>> >> Range("HH12:IO19").Select
>> >> Selection.Copy
>> >> ActiveWindow.LargeScroll ToRight:=-4
>> >> ActiveWindow.SmallScroll Down:=9
>> >> Range("X20:AG21").Select
>> >>
>> >> ActiveSheet.Protect Password:="justme"
>> >>
>> >>
>> >> Gord Dibben Excel MVP
>> >>
>> >> On Fri, 25 Nov 2005 11:51:40 -0800, "Connie Martin"
>> >> <[email protected]> wrote:
>> >>
>> >> >I have this simple macro, which works fine:
>> >> >
>> >> >Sub CopyFormDM()
>> >> >'
>> >> >' CopyFormDM Macro
>> >> >' Macro recorded 11/25/2005 by Connie Martin
>> >> >'
>> >> >
>> >> >'
>> >> > ActiveWindow.LargeScroll ToRight:=4
>> >> > Range("HH12:IO19").Select
>> >> > Selection.Copy
>> >> > ActiveWindow.LargeScroll ToRight:=-4
>> >> > ActiveWindow.SmallScroll Down:=9
>> >> > Range("X20:AG21").Select
>> >> >End Sub
>> >> >
>> >> >I would like to password protect the form and edit the macro so that part of
>> >> >the macro is to unprotect the form first, run the rest of the macro and then
>> >> >password protect it again before ending. When I recorded the macro that way
>> >> >and then later ran it, it stopped for me to put in the password, and then
>> >> >ended by protecting it without a password. I would like to edit the macro so
>> >> >that it puts the password in to unprotect and puts it in again to protect. I
>> >> >realize anyone looking at the macro in VB will see the password but this file
>> >> >is to be used by people who most likely don't know how to do that, and if
>> >> >they did, it would be no big harm done.
>> >> >
>> >> >Thank you
>> >> >Connie Martin
>> >>
>> >>
>>
>>