Code doesn't work unless worksheet is active

[bassnsjp]

I'm operating on Windows XP using MS Office 2003. Unfortunately the computer I'm using cannot connect to the internet so I'm limited in providing example code.

I'm using Excel to maintain a contact phone roster.
The Phone Roster worksheet contains the names and various phone numbers for everyone in the organization. Autofilter is on to facilitate selecting the desired subgroups of individuals from the list.

A Main Menu spreadsheet exists that have six command buttons. The user selects the corresponding button to obtain a display of the desired set of personnel.

I'm still in the development and test mode and am using VBE to execute, identify and resolve issues. I'm encountering two issues that I need assistance with.

Problem #1:
Upon opening the Roster Workbook the Main Menu worksheet is displayed and all is well. However, when a command button is selected I receive the following error message when this command is encountered.

Worksheets(Wksdisplay).Range("A" & strtsort & ":Y" & stopsort).Select

"Select method of Range class failed"

* Wksdisplay contains "Display Roster" which is a valid worksheet that is used to collect the data before it is displayed to the user.

* strtsort and stopsort contains the first and last rows of data that was just written to the Display Roster and will be sorted


At this point in time the Main Menu worksheet is active. However, if I manually select the Display Roster worksheet and press F5 to continue the marco works fine. What I would like to have done is the Display Roster worksheet get populated with corresponding personnel data, sorted and formatted then display the data by making the Display Roster the active worksheet. I don't want the user to view the data until it is completely built and ready for display.

Problem #2:
The last thing I would like to do before displaying the phone roster data is generate a command button in the Display Roster spreadsheet that the user would select to return back to the Main Menu. I recorded a macro of generating a command button, but what didn't get captured is the formatting of the command button. Such as the backcolor set to red, the text is bolded and white, and font is Arial. How can these attributes be set in VBA? Also, when code to create the command button is encountered in VBE I receive these error messages:

Can't enter break mode at this time
which is followed by
Object doesn't support this property or method

Here is the statement to create the command button:
Set myCmdObj = Worksheets(Wksdisplay).OLEObjects.Add(Classtype:="Forms.CommandButton.1", _
Link:=False, DisplayasIcon:=False, Left:=903.75, Top:=15,_
Width:+1.06, Height:=0.87)

Any assistance in resolving these issues would be greatly appreciated. Thank you in advance.

Steve

[Bob Phillips]

#1

Try

[vba]
Worksheets(Wksdisplay).Activate
Range("A" & strtsort & ":Y" & stopsort).Select[/vba]

I will get to #2 tomorrow unless someone else jumps in.

[bassnsjp]

XLD, appreciate your timely response. The Activate gets me past the error message, but it does display the worksheet and all the formatting functions performed to get it in the final format. I guess I'm going to have to live with that. Really was hoping to format the worksheet and upon completion display it to the user. It's weird though because there are other references to the worksheet and they are performed without having to activate or select the worksheet.

Any help with problem #2 would be appreciated, thank you in advance.
Steve