Code created in a Macro not working for a Command Button

[Amber_D_Laws]

Ok...I created a macro to give me a head start with a command button. I need the VBA coding to select all the cells on a given worksheet. The macro used:

Cells.Select

All sources I have seen so far indicate that this should be correct, however, once the code was copied from the macro to the command button I keep getting the following error:

Run-time error '1004':
Select method of Range class failed.

So, why on earth does it work in the macro, and not for the command button?
Any ideas?

Thanks,
Amber

[Ron de Bruin]

Hi Amber

Do you use Excel 97 ?

Change the takefocusonclick in the properties of the button to false

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" <[email protected]> wrote in message
news:[email protected]...
>
> Ok...I created a macro to give me a head start with a command button. I
> need the VBA coding to select all the cells on a given worksheet. The
> macro used:
>
> Cells.Select
>
> All sources I have seen so far indicate that this should be correct,
> however, once the code was copied from the macro to the command button
> I keep getting the following error:
>
> Run-time error '1004':
> Select method of Range class failed.
>
> So, why on earth does it work in the macro, and not for the command
> button?
> Any ideas?
>
> Thanks,
> Amber
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=500357
>

[Amber_D_Laws]

No luck. I tried your suggestion, and it had no effect.
I am using Excel 2000.
Eventhought your suggestion did not work, thanks for trying!

[Ron de Bruin]

Hi Amber

If you want you can send me the workbook private then I look at it for you

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" <[email protected]> wrote in message
news:[email protected]...
>
> No luck. I tried your suggestion, and it had no effect.
> I am using Excel 2000.
> Eventhought your suggestion did not work, thanks for trying!
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=500357
>

[Amber_D_Laws]

Thanks Ron,

I really appreciate the offer. It is a quote module for my company, I will show you the code here, and if you are not able to puzzle it out from there, I will email the file to you.
It has a whole lot of password protection on it, confidential information, and it's really big. So, I am sure you can see why I am reticent about sending it.

Here is the code:

Please Login or Register  to view this content.

[Amber_D_Laws]

I forgot to tell you where to look, and exactly what I am trying to get the thing to do. Sorry.

This button is supposed to protect the file and lock it up the file so that the client my user will be sending the file to can look at it and print it, but nothing else.
My problem is in step 2 where on the "QUOTE" sheet itself, I am hiding some user notes, and locking cells that were previously unlocked. This is where I have the attempt to select all the cells that is giving me the run-time error.

As I mentioned previously, I created the macro, edited it with a few extras you can't get from macro recording, and tested it by running the macro. It worked like a charm. My problem started when I transfered that coding into the sub for the command button.

Thanks again Ron.

[Amber_D_Laws]

Hello again,

Ron seems to have left my thread by the wayside, and my problem is still not solved. Please see the above posts (also quoted below). If anyone has any ideas, please let me know.

Thanks,
Amber

_________________________________________________________________

01-11-2006 04:21 PM
Amber_D_Laws I forgot to tell you where to look, and exactly what I am trying to get the thing to do. Sorry.

This button is supposed to protect the file and lock it up the file so that the client my user will be sending the file to can look at it and print it, but nothing else.
My problem is in step 2 where on the "QUOTE" sheet itself, I am hiding some user notes, and locking cells that were previously unlocked. This is where I have the attempt to select all the cells that is giving me the run-time error.

As I mentioned previously, I created the macro, edited it with a few extras you can't get from macro recording, and tested it by running the macro. It worked like a charm. My problem started when I transfered that coding into the sub for the command button.

Thanks again Ron.
01-11-2006 04:15 PM
Amber_D_Laws Thanks Ron,

I really appreciate the offer. It is a quote module for my company, I will show you the code here, and if you are not able to puzzle it out from there, I will email the file to you.
It has a whole lot of password protection on it, confidential information, and it's really big. So, I am sure you can see why I am reticent about sending it.

Here is the code:


Code:
Private Sub CommandButton2_Click()
'Makes the quote email ready
'
'
'Prep 1 - Declares the variables for the file name to be used in the "saved as" function
Dim QNum As String
Dim CNam As String
Dim CrDt As String
Dim VNum As String
'
'Prep 2 - Defines the variable names from Quote Form
QNum = Range("X10").Text
CNam = Range("O19").Text
CrDt = Format(Now, "mmddyy")
VNum = Range("AB10").Text
'
'Step 1 - Save, and hide all non-essential sheets
ActiveWorkbook.Save
ActiveWindow.SelectedSheets.Visible = False
ActiveWindow.SelectedSheets.Visible = False
ActiveWindow.SelectedSheets.Visible = False
'
'Step 2 - Locks all cells, hides user notes, and re-hides the column and row headings
ActiveWindow.DisplayHeadings = True
ActiveSheet.Unprotect Password:="STLMOB@900"
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = False
Columns("AD:AI").Select
Range("AI1").Activate
Selection.EntireColumn.Hidden = True
ActiveWindow.DisplayHeadings = False
'
'Step 3 - Protects the worksheet and workbook
ActiveSheet.Protect Password:="STLMOB@900", DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlNoSelection
ActiveWorkbook.Protect Password:="STLMOB@900", Structure:=True, Windows:=False
'
'Step 4 - Saves the protected file with a unique name, and makes it read-only recommended
ActiveWorkbook.SaveAs Filename:= _
"X:\_FEE SCHEDULE & QUOTE MODULE\Created Quotes\" _
& QNum & "-" & CNam & "-" & CrDt & " _ver " & VNum & " - EMAIL" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="STLMOB@900", _
ReadOnlyRecommended:=True, CreateBackup:=False
'
'Step 5 - Displays a message informing the user that the protection is complete
MsgBox "Your quote has been successfully protected." & _
Chr(13) & "It is now safe to email.", 64, "Security Check Complete!"
End Sub
01-11-2006 03:10 PM
Ron de Bruin Hi Amber

If you want you can send me the workbook private then I look at it for you

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" <[email protected]> wrote in message
news:Amber_D_Laws.21hm8c_1137011704.9758@excelforu m-nospam.com...
>
> No luck. I tried your suggestion, and it had no effect.
> I am using Excel 2000.
> Eventhought your suggestion did not work, thanks for trying!
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=500357
>



01-11-2006 02:32 PM
Amber_D_Laws No luck. I tried your suggestion, and it had no effect.
I am using Excel 2000.
Eventhought your suggestion did not work, thanks for trying!
01-11-2006 02:25 PM
Ron de Bruin Hi Amber

Do you use Excel 97 ?

Change the takefocusonclick in the properties of the button to false

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" <[email protected]> wrote in message
news:Amber_D_Laws.21hlay_1137010503.0178@excelforu m-nospam.com...
>
> Ok...I created a macro to give me a head start with a command button. I
> need the VBA coding to select all the cells on a given worksheet. The
> macro used:
>
> Cells.Select
>
> All sources I have seen so far indicate that this should be correct,
> however, once the code was copied from the macro to the command button
> I keep getting the following error:
>
> Run-time error '1004':
> Select method of Range class failed.
>
> So, why on earth does it work in the macro, and not for the command
> button?
> Any ideas?
>
> Thanks,
> Amber
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=500357
>



01-11-2006 02:12 PM
Amber_D_Laws Ok...I created a macro to give me a head start with a command button. I need the VBA coding to select all the cells on a given worksheet. The macro used:

Cells.Select

All sources I have seen so far indicate that this should be correct, however, once the code was copied from the macro to the command button I keep getting the following error:

Run-time error '1004':
Select method of Range class failed.

So, why on earth does it work in the macro, and not for the command button?
Any ideas?

Thanks,
Amber

[Ron de Bruin]

I flag this thread Amber but I am very busy on this moment.
I try to look at it this evening


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" <[email protected]> wrote in message
news:[email protected]...
>
> Hello again,
>
> Ron seems to have left my thread by the wayside, and my problem is
> still not solved. Please see the above posts (also quoted below). If
> anyone has any ideas, please let me know.
>
> Thanks,
> Amber
>
> _________________________________________________________________
>
> 01-11-2006 04:21 PM
> Amber_D_Laws I forgot to tell you where to look, and exactly what I am
> trying to get the thing to do. Sorry.
>
> This button is supposed to protect the file and lock it up the file so
> that the client my user will be sending the file to can look at it and
> print it, but nothing else.
> My problem is in step 2 where on the "QUOTE" sheet itself, I am hiding
> some user notes, and locking cells that were previously unlocked. This
> is where I have the attempt to select all the cells that is giving me
> the run-time error.
>
> As I mentioned previously, I created the macro, edited it with a few
> extras you can't get from macro recording, and tested it by running the
> macro. It worked like a charm. My problem started when I transfered that
> coding into the sub for the command button.
>
> Thanks again Ron.
> 01-11-2006 04:15 PM
> Amber_D_Laws Thanks Ron,
>
> I really appreciate the offer. It is a quote module for my company, I
> will show you the code here, and if you are not able to puzzle it out
> from there, I will email the file to you.
> It has a whole lot of password protection on it, confidential
> information, and it's really big. So, I am sure you can see why I am
> reticent about sending it.
>
> Here is the code:
>
>
> Code:
> Private Sub CommandButton2_Click()
> 'Makes the quote email ready
> '
> '
> 'Prep 1 - Declares the variables for the file name to be used in the
> "saved as" function
> Dim QNum As String
> Dim CNam As String
> Dim CrDt As String
> Dim VNum As String
> '
> 'Prep 2 - Defines the variable names from Quote Form
> QNum = Range("X10").Text
> CNam = Range("O19").Text
> CrDt = Format(Now, "mmddyy")
> VNum = Range("AB10").Text
> '
> 'Step 1 - Save, and hide all non-essential sheets
> ActiveWorkbook.Save
> ActiveWindow.SelectedSheets.Visible = False
> ActiveWindow.SelectedSheets.Visible = False
> ActiveWindow.SelectedSheets.Visible = False
> '
> 'Step 2 - Locks all cells, hides user notes, and re-hides the column
> and row headings
> ActiveWindow.DisplayHeadings = True
> ActiveSheet.Unprotect Password:="STLMOB@900"
> Cells.Select
> Selection.Locked = True
> Selection.FormulaHidden = False
> Columns("AD:AI").Select
> Range("AI1").Activate
> Selection.EntireColumn.Hidden = True
> ActiveWindow.DisplayHeadings = False
> '
> 'Step 3 - Protects the worksheet and workbook
> ActiveSheet.Protect Password:="STLMOB@900", DrawingObjects:=True,
> Contents:=True, Scenarios:=True
> ActiveSheet.EnableSelection = xlNoSelection
> ActiveWorkbook.Protect Password:="STLMOB@900", Structure:=True,
> Windows:=False
> '
> 'Step 4 - Saves the protected file with a unique name, and makes it
> read-only recommended
> ActiveWorkbook.SaveAs Filename:= _
> "X:\_FEE SCHEDULE & QUOTE MODULE\Created Quotes\" _
> & QNum & "-" & CNam & "-" & CrDt & " _ver " & VNum & " - EMAIL"
> _
> , FileFormat:=xlNormal, Password:="",
> WriteResPassword:="STLMOB@900", _
> ReadOnlyRecommended:=True, CreateBackup:=False
> '
> 'Step 5 - Displays a message informing the user that the protection is
> complete
> MsgBox "Your quote has been successfully protected." & _
> Chr(13) & "It is now safe to email.", 64, "Security Check
> Complete!"
> End Sub
> 01-11-2006 03:10 PM
> Ron de Bruin Hi Amber
>
> If you want you can send me the workbook private then I look at it for
> you
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Amber_D_Laws"
> <[email protected]> wrote in
> message
> news:Amber_D_Laws.21hm8c_1137011704.9758@excelforu m-nospam.com...
>>
>> No luck. I tried your suggestion, and it had no effect.
>> I am using Excel 2000.
>> Eventhought your suggestion did not work, thanks for trying!
>>
>>
>> --
>> Amber_D_Laws
>>
> ------------------------------------------------------------------------
>> Amber_D_Laws's Profile:
> http://www.excelforum.com/member.php...o&userid=30012
>> View this thread:
> http://www.excelforum.com/showthread...hreadid=500357
>>
>
>
>
> 01-11-2006 02:32 PM
> Amber_D_Laws No luck. I tried your suggestion, and it had no effect.
> I am using Excel 2000.
> Eventhought your suggestion did not work, thanks for trying!
> 01-11-2006 02:25 PM
> Ron de Bruin Hi Amber
>
> Do you use Excel 97 ?
>
> Change the takefocusonclick in the properties of the button to false
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Amber_D_Laws"
> <[email protected]> wrote in
> message
> news:Amber_D_Laws.21hlay_1137010503.0178@excelforu m-nospam.com...
>>
>> Ok...I created a macro to give me a head start with a command button.
> I
>> need the VBA coding to select all the cells on a given worksheet.
> The
>> macro used:
>>
>> Cells.Select
>>
>> All sources I have seen so far indicate that this should be correct,
>> however, once the code was copied from the macro to the command
> button
>> I keep getting the following error:
>>
>> Run-time error '1004':
>> Select method of Range class failed.
>>
>> So, why on earth does it work in the macro, and not for the command
>> button?
>> Any ideas?
>>
>> Thanks,
>> Amber
>>
>>
>> --
>> Amber_D_Laws
>>
> ------------------------------------------------------------------------
>> Amber_D_Laws's Profile:
> http://www.excelforum.com/member.php...o&userid=30012
>> View this thread:
> http://www.excelforum.com/showthread...hreadid=500357
>>
>
>
>
> 01-11-2006 02:12 PM
> Amber_D_Laws Ok...I created a macro to give me a head start with a
> command button. I need the VBA coding to select all the cells on a
> given worksheet. The macro used:
>
> Cells.Select
>
> All sources I have seen so far indicate that this should be correct,
> however, once the code was copied from the macro to the command button
> I keep getting the following error:
>
> Run-time error '1004':
> Select method of Range class failed.
>
> So, why on earth does it work in the macro, and not for the command
> button?
> Any ideas?
>
> Thanks,
> Amber
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=500357
>

[Amber_D_Laws]

Thanks Ron!

I didn't mean to seem ungreatful. I know the experts have hectic schedules. I just thought something bigger had gotten your attention, and I didn't want to hound you about it. I didn't mean to insult or slight you in any way, and I appologize if I did.

I will look forward to your reply then.

Until later,
Amber

I flag this thread Amber but I am very busy on this moment.
I try to look at it this evening


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" <[email protected]> wrote in message
news:[email protected]...
>
> Hello again,
>
> Ron seems to have left my thread by the wayside, and my problem is
> still not solved. Please see the above posts (also quoted below). If
> anyone has any ideas, please let me know.
>
> Thanks,
> Amber
>
> _________________________________________________________________
>
> 01-11-2006 04:21 PM
> Amber_D_Laws I forgot to tell you where to look, and exactly what I am
> trying to get the thing to do. Sorry.
>
> This button is supposed to protect the file and lock it up the file so
> that the client my user will be sending the file to can look at it and
> print it, but nothing else.
> My problem is in step 2 where on the "QUOTE" sheet itself, I am hiding
> some user notes, and locking cells that were previously unlocked. This
> is where I have the attempt to select all the cells that is giving me
> the run-time error.
>
> As I mentioned previously, I created the macro, edited it with a few
> extras you can't get from macro recording, and tested it by running the
> macro. It worked like a charm. My problem started when I transfered that
> coding into the sub for the command button.
>
> Thanks again Ron.
> 01-11-2006 04:15 PM
> Amber_D_Laws Thanks Ron,
>
> I really appreciate the offer. It is a quote module for my company, I
> will show you the code here, and if you are not able to puzzle it out
> from there, I will email the file to you.
> It has a whole lot of password protection on it, confidential
> information, and it's really big. So, I am sure you can see why I am
> reticent about sending it.
>
> Here is the code:
>
>
> Code:
> Private Sub CommandButton2_Click()
> 'Makes the quote email ready
> '
> '
> 'Prep 1 - Declares the variables for the file name to be used in the
> "saved as" function
> Dim QNum As String
> Dim CNam As String
> Dim CrDt As String
> Dim VNum As String
> '
> 'Prep 2 - Defines the variable names from Quote Form
> QNum = Range("X10").Text
> CNam = Range("O19").Text
> CrDt = Format(Now, "mmddyy")
> VNum = Range("AB10").Text
> '
> 'Step 1 - Save, and hide all non-essential sheets
> ActiveWorkbook.Save
> ActiveWindow.SelectedSheets.Visible = False
> ActiveWindow.SelectedSheets.Visible = False
> ActiveWindow.SelectedSheets.Visible = False
> '
> 'Step 2 - Locks all cells, hides user notes, and re-hides the column
> and row headings
> ActiveWindow.DisplayHeadings = True
> ActiveSheet.Unprotect Password:="STLMOB@900"
> Cells.Select
> Selection.Locked = True
> Selection.FormulaHidden = False
> Columns("AD:AI").Select
> Range("AI1").Activate
> Selection.EntireColumn.Hidden = True
> ActiveWindow.DisplayHeadings = False
> '
> 'Step 3 - Protects the worksheet and workbook
> ActiveSheet.Protect Password:="STLMOB@900", DrawingObjects:=True,
> Contents:=True, Scenarios:=True
> ActiveSheet.EnableSelection = xlNoSelection
> ActiveWorkbook.Protect Password:="STLMOB@900", Structure:=True,
> Windows:=False
> '
> 'Step 4 - Saves the protected file with a unique name, and makes it
> read-only recommended
> ActiveWorkbook.SaveAs Filename:= _
> "X:\_FEE SCHEDULE & QUOTE MODULE\Created Quotes\" _
> & QNum & "-" & CNam & "-" & CrDt & " _ver " & VNum & " - EMAIL"
> _
> , FileFormat:=xlNormal, Password:="",
> WriteResPassword:="STLMOB@900", _
> ReadOnlyRecommended:=True, CreateBackup:=False
> '
> 'Step 5 - Displays a message informing the user that the protection is
> complete
> MsgBox "Your quote has been successfully protected." & _
> Chr(13) & "It is now safe to email.", 64, "Security Check
> Complete!"
> End Sub
> 01-11-2006 03:10 PM
> Ron de Bruin Hi Amber
>
> If you want you can send me the workbook private then I look at it for
> you
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Amber_D_Laws"
> <[email protected]> wrote in
> message
> news:Amber_D_Laws.21hm8c_1137011704.9758@excelforu m-nospam.com...
>>
>> No luck. I tried your suggestion, and it had no effect.
>> I am using Excel 2000.
>> Eventhought your suggestion did not work, thanks for trying!
>>
>>
>> --
>> Amber_D_Laws
>>
> ------------------------------------------------------------------------
>> Amber_D_Laws's Profile:
> http://www.excelforum.com/member.php...o&userid=30012
>> View this thread:
> http://www.excelforum.com/showthread...hreadid=500357
>>
>
>
>
> 01-11-2006 02:32 PM
> Amber_D_Laws No luck. I tried your suggestion, and it had no effect.
> I am using Excel 2000.
> Eventhought your suggestion did not work, thanks for trying!
> 01-11-2006 02:25 PM
> Ron de Bruin Hi Amber
>
> Do you use Excel 97 ?
>
> Change the takefocusonclick in the properties of the button to false
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Amber_D_Laws"
> <[email protected]> wrote in
> message
> news:Amber_D_Laws.21hlay_1137010503.0178@excelforu m-nospam.com...
>>
>> Ok...I created a macro to give me a head start with a command button.
> I
>> need the VBA coding to select all the cells on a given worksheet.
> The
>> macro used:
>>
>> Cells.Select
>>
>> All sources I have seen so far indicate that this should be correct,
>> however, once the code was copied from the macro to the command
> button
>> I keep getting the following error:
>>
>> Run-time error '1004':
>> Select method of Range class failed.
>>
>> So, why on earth does it work in the macro, and not for the command
>> button?
>> Any ideas?
>>
>> Thanks,
>> Amber
>>
>>
>> --
>> Amber_D_Laws
>>
> ------------------------------------------------------------------------
>> Amber_D_Laws's Profile:
> http://www.excelforum.com/member.php...o&userid=30012
>> View this thread:
> http://www.excelforum.com/showthread...hreadid=500357
>>
>
>
>
> 01-11-2006 02:12 PM
> Amber_D_Laws Ok...I created a macro to give me a head start with a
> command button. I need the VBA coding to select all the cells on a
> given worksheet. The macro used:
>
> Cells.Select
>
> All sources I have seen so far indicate that this should be correct,
> however, once the code was copied from the macro to the command button
> I keep getting the following error:
>
> Run-time error '1004':
> Select method of Range class failed.
>
> So, why on earth does it work in the macro, and not for the command
> button?
> Any ideas?
>
> Thanks,
> Amber
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=500357
>

[Ron de Bruin]

Hi Amber

I don't understand this part in your code (3 times)

>> > ActiveWindow.SelectedSheets.Visible = False
>> > ActiveWindow.SelectedSheets.Visible = False
>> > ActiveWindow.SelectedSheets.Visible = False

Do you weant to hide all sheets except one ???
What do you select before you run this

Take a fast look and test this part without a problem


'Step 1 - Save, and hide all non-essential sheets
ActiveWorkbook.Save
'ActiveWindow.SelectedSheets.Visible = False
'
'Step 2 - Locks all cells, hides user notes, and re-hides the column and row headings
ActiveWindow.DisplayHeadings = True
ActiveSheet.Unprotect Password:="STLMOB@900"
With Cells
.Locked = True
.FormulaHidden = False
End With
Columns("AD:AI").Hidden = True
ActiveWindow.DisplayHeadings = False



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks Ron!
>
> I didn't mean to seem ungreatful. I know the experts have hectic
> schedules. I just thought something bigger had gotten your attention,
> and I didn't want to hound you about it. I didn't mean to insult or
> slight you in any way, and I appologize if I did.
>
> I will look forward to your reply then.
>
> Until later,
> Amber
>
>
> Ron de Bruin Wrote:
>> I flag this thread Amber but I am very busy on this moment.
>> I try to look at it this evening
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "Amber_D_Laws"
>> <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >
>> > Hello again,
>> >
>> > Ron seems to have left my thread by the wayside, and my problem is
>> > still not solved. Please see the above posts (also quoted below). If
>> > anyone has any ideas, please let me know.
>> >
>> > Thanks,
>> > Amber
>> >
>> > _________________________________________________________________
>> >
>> > 01-11-2006 04:21 PM
>> > Amber_D_Laws I forgot to tell you where to look, and exactly what I
>> am
>> > trying to get the thing to do. Sorry.
>> >
>> > This button is supposed to protect the file and lock it up the file
>> so
>> > that the client my user will be sending the file to can look at it
>> and
>> > print it, but nothing else.
>> > My problem is in step 2 where on the "QUOTE" sheet itself, I am
>> hiding
>> > some user notes, and locking cells that were previously unlocked.
>> This
>> > is where I have the attempt to select all the cells that is giving
>> me
>> > the run-time error.
>> >
>> > As I mentioned previously, I created the macro, edited it with a few
>> > extras you can't get from macro recording, and tested it by running
>> the
>> > macro. It worked like a charm. My problem started when I transfered
>> that
>> > coding into the sub for the command button.
>> >
>> > Thanks again Ron.
>> > 01-11-2006 04:15 PM
>> > Amber_D_Laws Thanks Ron,
>> >
>> > I really appreciate the offer. It is a quote module for my company,
>> I
>> > will show you the code here, and if you are not able to puzzle it
>> out
>> > from there, I will email the file to you.
>> > It has a whole lot of password protection on it, confidential
>> > information, and it's really big. So, I am sure you can see why I am
>> > reticent about sending it.
>> >
>> > Here is the code:
>> >
>> >
>> > Code:
>> > Private Sub CommandButton2_Click()
>> > 'Makes the quote email ready
>> > '
>> > '
>> > 'Prep 1 - Declares the variables for the file name to be used in the
>> > "saved as" function
>> > Dim QNum As String
>> > Dim CNam As String
>> > Dim CrDt As String
>> > Dim VNum As String
>> > '
>> > 'Prep 2 - Defines the variable names from Quote Form
>> > QNum = Range("X10").Text
>> > CNam = Range("O19").Text
>> > CrDt = Format(Now, "mmddyy")
>> > VNum = Range("AB10").Text
>> > '
>> > 'Step 1 - Save, and hide all non-essential sheets
>> > ActiveWorkbook.Save
>> > ActiveWindow.SelectedSheets.Visible = False
>> > ActiveWindow.SelectedSheets.Visible = False
>> > ActiveWindow.SelectedSheets.Visible = False
>> > '
>> > 'Step 2 - Locks all cells, hides user notes, and re-hides the
>> column
>> > and row headings
>> > ActiveWindow.DisplayHeadings = True
>> > ActiveSheet.Unprotect Password:="STLMOB@900"
>> > Cells.Select
>> > Selection.Locked = True
>> > Selection.FormulaHidden = False
>> > Columns("AD:AI").Select
>> > Range("AI1").Activate
>> > Selection.EntireColumn.Hidden = True
>> > ActiveWindow.DisplayHeadings = False
>> > '
>> > 'Step 3 - Protects the worksheet and workbook
>> > ActiveSheet.Protect Password:="STLMOB@900", DrawingObjects:=True,
>> > Contents:=True, Scenarios:=True
>> > ActiveSheet.EnableSelection = xlNoSelection
>> > ActiveWorkbook.Protect Password:="STLMOB@900", Structure:=True,
>> > Windows:=False
>> > '
>> > 'Step 4 - Saves the protected file with a unique name, and makes it
>> > read-only recommended
>> > ActiveWorkbook.SaveAs Filename:= _
>> > "X:\_FEE SCHEDULE & QUOTE MODULE\Created Quotes\" _
>> > & QNum & "-" & CNam & "-" & CrDt & " _ver " & VNum & " - EMAIL"
>> > _
>> > , FileFormat:=xlNormal, Password:="",
>> > WriteResPassword:="STLMOB@900", _
>> > ReadOnlyRecommended:=True, CreateBackup:=False
>> > '
>> > 'Step 5 - Displays a message informing the user that the protection
>> is
>> > complete
>> > MsgBox "Your quote has been successfully protected." & _
>> > Chr(13) & "It is now safe to email.", 64, "Security Check
>> > Complete!"
>> > End Sub
>> > 01-11-2006 03:10 PM
>> > Ron de Bruin Hi Amber
>> >
>> > If you want you can send me the workbook private then I look at it
>> for
>> > you
>> >
>> > --
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl
>> >
>> >
>> > "Amber_D_Laws"
>> > <[email protected]> wrote in
>> > message
>> > news:Amber_D_Laws.21hm8c_1137011704.9758@excelforu m-nospam.com...
>> >>
>> >> No luck. I tried your suggestion, and it had no effect.
>> >> I am using Excel 2000.
>> >> Eventhought your suggestion did not work, thanks for trying!
>> >>
>> >>
>> >> --
>> >> Amber_D_Laws
>> >>
>> >
>> ------------------------------------------------------------------------
>> >> Amber_D_Laws's Profile:
>> > http://www.excelforum.com/member.php...o&userid=30012
>> >> View this thread:
>> > http://www.excelforum.com/showthread...hreadid=500357
>> >>
>> >
>> >
>> >
>> > 01-11-2006 02:32 PM
>> > Amber_D_Laws No luck. I tried your suggestion, and it had no effect.
>> > I am using Excel 2000.
>> > Eventhought your suggestion did not work, thanks for trying!
>> > 01-11-2006 02:25 PM
>> > Ron de Bruin Hi Amber
>> >
>> > Do you use Excel 97 ?
>> >
>> > Change the takefocusonclick in the properties of the button to false
>> >
>> > --
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl
>> >
>> >
>> > "Amber_D_Laws"
>> > <[email protected]> wrote in
>> > message
>> > news:Amber_D_Laws.21hlay_1137010503.0178@excelforu m-nospam.com...
>> >>
>> >> Ok...I created a macro to give me a head start with a command
>> button.
>> > I
>> >> need the VBA coding to select all the cells on a given worksheet.
>> > The
>> >> macro used:
>> >>
>> >> Cells.Select
>> >>
>> >> All sources I have seen so far indicate that this should be
>> correct,
>> >> however, once the code was copied from the macro to the command
>> > button
>> >> I keep getting the following error:
>> >>
>> >> Run-time error '1004':
>> >> Select method of Range class failed.
>> >>
>> >> So, why on earth does it work in the macro, and not for the command
>> >> button?
>> >> Any ideas?
>> >>
>> >> Thanks,
>> >> Amber
>> >>
>> >>
>> >> --
>> >> Amber_D_Laws
>> >>
>> >
>> ------------------------------------------------------------------------
>> >> Amber_D_Laws's Profile:
>> > http://www.excelforum.com/member.php...o&userid=30012
>> >> View this thread:
>> > http://www.excelforum.com/showthread...hreadid=500357
>> >>
>> >
>> >
>> >
>> > 01-11-2006 02:12 PM
>> > Amber_D_Laws Ok...I created a macro to give me a head start with a
>> > command button. I need the VBA coding to select all the cells on a
>> > given worksheet. The macro used:
>> >
>> > Cells.Select
>> >
>> > All sources I have seen so far indicate that this should be correct,
>> > however, once the code was copied from the macro to the command
>> button
>> > I keep getting the following error:
>> >
>> > Run-time error '1004':
>> > Select method of Range class failed.
>> >
>> > So, why on earth does it work in the macro, and not for the command
>> > button?
>> > Any ideas?
>> >
>> > Thanks,
>> > Amber
>> >
>> >
>> > --
>> > Amber_D_Laws
>> >
>> ------------------------------------------------------------------------
>> > Amber_D_Laws's Profile:
>> http://www.excelforum.com/member.php...o&userid=30012
>> > View this thread:
>> http://www.excelforum.com/showthread...hreadid=500357
>> >
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=500357
>

[Amber_D_Laws]

The three ActiveWindow.SelectedSheets.Visible = False are what came out of the macro, it never occured to me to make it more efficant. Thanks for the tip!

As for the locking of the cells, I tried your code, and at least it is giving me a different error instead of the one I've been getting. It's not much progress, but it's something....

It now says:

"Run-time error '1004':
Unable to set the locked property of the Range class



The code is now:

Please Login or Register  to view this content.

I refuse to believe that this won't work somehow. Thanks for sticking with me.
Amber

[QUOTE=Ron de Bruin]Hi Amber

I don't understand this part in your code (3 times)

>> > ActiveWindow.SelectedSheets.Visible = False
>> > ActiveWindow.SelectedSheets.Visible = False
>> > ActiveWindow.SelectedSheets.Visible = False

Do you weant to hide all sheets except one ???
What do you select before you run this

Take a fast look and test this part without a problem


'Step 1 - Save, and hide all non-essential sheets
ActiveWorkbook.Save
'ActiveWindow.SelectedSheets.Visible = False
'
'Step 2 - Locks all cells, hides user notes, and re-hides the column and row headings
ActiveWindow.DisplayHeadings = True
ActiveSheet.Unprotect Password:="STLMOB@900"
With Cells
.Locked = True
.FormulaHidden = False
End With
Columns("AD:AI").Hidden = True
ActiveWindow.DisplayHeadings = False



--
Regards Ron de Bruin
http://www.rondebruin.nl

[Ron de Bruin]

how many sheets do you have in this workbook

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" <[email protected]> wrote in message
news:[email protected]...
>
> The three ActiveWindow.SelectedSheets.Visible = False are what came out
> of the macro, it never occured to me to make it more efficant. Thanks
> for the tip!
>
> As for the locking of the cells, I tried your code, and at least it is
> giving me a different error instead of the one I've been getting. It's
> not much progress, but it's something....
>
> It now says:
>
> "Run-time error '1004':
> Unable to set the locked property of the Range class
>
>
>
> The code is now:
>
>
> Code:
> --------------------
> 'Step 1 - Save, and hide all non-essential sheets
> ActiveWorkbook.Save
> Sheets(Array("READ ME", "E-Signature", "Forms")).Select
> Sheets("READ ME").Activate
> ActiveWindow.SelectedSheets.Visible = False
> '
> 'Step 2 - Locks all cells, hides user notes, and re-hides the column and row headings
> ActiveWindow.DisplayHeadings = True
> ActiveSheet.Unprotect Password:="STLMOB@900"
> With Cells
> .Locked = True
> .FormulaHidden = False
> End With
> Columns("AD:AI").Hidden = True
> ActiveWindow.DisplayHeadings = False
> --------------------
>
>
> I refuse to believe that this won't work somehow. Thanks for sticking
> with me.
> Amber
>
> Ron de Bruin Wrote:
>> Hi Amber
>>
>> I don't understand this part in your code (3 times)
>>
>> >> > ActiveWindow.SelectedSheets.Visible = False
>> >> > ActiveWindow.SelectedSheets.Visible = False
>> >> > ActiveWindow.SelectedSheets.Visible = False
>>
>> Do you weant to hide all sheets except one ???
>> What do you select before you run this
>>
>> Take a fast look and test this part without a problem
>>
>>
>> 'Step 1 - Save, and hide all non-essential sheets
>> ActiveWorkbook.Save
>> 'ActiveWindow.SelectedSheets.Visible = False
>> '
>> 'Step 2 - Locks all cells, hides user notes, and re-hides the column
>> and row headings
>> ActiveWindow.DisplayHeadings = True
>> ActiveSheet.Unprotect Password:="STLMOB@900"
>> With Cells
>> .Locked = True
>> .FormulaHidden = False
>> End With
>> Columns("AD:AI").Hidden = True
>> ActiveWindow.DisplayHeadings = False
>>
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=500357
>

[Amber_D_Laws]

Six Total:
Four that are visable to my users, and two that contain references for formulas that remain hidden all the time.

[Ron de Bruin]

Can you duplicate this in a test workbook with also 6 sheets
Send me that one

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" <[email protected]> wrote in message
news:[email protected]...
>
> Six Total:
> Four that are visable to my users, and two that contain references for
> formulas that remain hidden all the time.
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=500357
>

[Amber_D_Laws]

See the email I sent you.

[Ron de Bruin]

Problem solved

To difficult to explain here

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Amber_D_Laws" <[email protected]> wrote in message
news:[email protected]...
>
> See the email I sent you.
>
>
> --
> Amber_D_Laws
> ------------------------------------------------------------------------
> Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
> View this thread: http://www.excelforum.com/showthread...hreadid=500357
>