IF statement to activate worksheet & select Range

Hi All,

I have a Workbook (don't we all if we're looking for help here), with a
worksheet at the end that I have several command buttons on. The first
command button will insert a new worksheet that uses a variable as part of
the naming (i.e. Rename to "Formula Sheet #" & <variable>) and inserts the
sheet before the "command button sheet." It is working successfully as far
as inserting and naming goes. I have the varible declared in the Public area
so I can use it for the rest of the buttons.

The rest of the buttons have a macro attached to them with an IF statement.
They are supposed to activate Formula Sheet #<variable> and using the IF at
this point select a range based on the criteria. The code for the whole
thing is as follows

Worksheets("Formula Sheet #" & FormSheet).Activate
If LeftRight / 2 = FormatNumber(LeftRight / 2, 0) Then
FirstRow = Cells(Rows.Count, "A").End(xlUp).Offset(2, 0).Row
FirstCol = Cells(FirstRow, Columns.Count).End(xlToLeft).Column
Range(Cells(FirstRow, FirstCol), Cells(FirstRow + 6, FirstCol + 2)).Select
Else
FirstRow = Cells(FirstRow, FirstCol).End(xlUp).Offset(2, 0).Row
FirstCol = Cells(FirstRow, Columns.Count).End(xlToLeft).Offset(0,
4).Column
Range(Cells(FirstRow, FirstCol), Cells(FirstRow + 6, FirstCol + 2)).Select
End If

The IF portion determines where the range will be selected based on whether
or not LeftRight is even/odd. Basically the "odd" time any of the "range
selection buttons" are used the group of cells to the far left will be
selected and merged where an even LeftRight will select the range a couple of
cells to the right of the previous range and merge it. The problem I am
having is that if I have 'Worksheets("Formula Sheet #" & FormSheet).Activate'
statement in there I get an error. I've tried putting it in various
locations and still get the same error. If I leave it out, the Ranges on the
command button sheet get selected and merged in the same manner as I want the
ranges on the Formula Sheet to be selected and merged.

Also, I have tried moving the actual Range Selection statement to a location
outside of the IF Then but get a 400 error. If anyone has any ideas on how
to activate the formula sheet and select the ranges based on the LeftRight
idea please let me know. All the variable are public so they can be used
with any of the command buttons.

Thanks,

Chaz

Hi Chaz,


What is the text of the error message that you get?

Add an error handler to your code

ERROR HANDLER CODE:

put this at the top of your program, right after the
procedure declaration (skip a line first for better readability)

'~~~~~~~~~~~~~~~~~~~~~~
On Error GoTo Proc_Err

'~~~~~~~~~~~~~~~~~~~~~~
... then your statements
'~~~~~~~~~~~~~~~~~~~~~~

put this at the end of the program

'~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
On Error Resume Next
'release object variables if any
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit

'~~~~~~~~~~~~~~~~~~~~~~



instead of
Worksheets("Formula Sheet #" & FormSheet).Activate

try this:
Sheets("Formula Sheet #" & FormSheet).Select

If you get an error, check the value of FormSheet when the
code Stops (hover over the variable with your mouse and look
at what pops up)


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

MentalDrow wrote:
> Hi All,
>
> I have a Workbook (don't we all if we're looking for help here), with a
> worksheet at the end that I have several command buttons on. The first
> command button will insert a new worksheet that uses a variable as part of
> the naming (i.e. Rename to "Formula Sheet #" & <variable>) and inserts the
> sheet before the "command button sheet." It is working successfully as far
> as inserting and naming goes. I have the varible declared in the Public area
> so I can use it for the rest of the buttons.
>
> The rest of the buttons have a macro attached to them with an IF statement.
> They are supposed to activate Formula Sheet #<variable> and using the IF at
> this point select a range based on the criteria. The code for the whole
> thing is as follows
>
> Worksheets("Formula Sheet #" & FormSheet).Activate
> If LeftRight / 2 = FormatNumber(LeftRight / 2, 0) Then
> FirstRow = Cells(Rows.Count, "A").End(xlUp).Offset(2, 0).Row
> FirstCol = Cells(FirstRow, Columns.Count).End(xlToLeft).Column
> Range(Cells(FirstRow, FirstCol), Cells(FirstRow + 6, FirstCol + 2)).Select
> Else
> FirstRow = Cells(FirstRow, FirstCol).End(xlUp).Offset(2, 0).Row
> FirstCol = Cells(FirstRow, Columns.Count).End(xlToLeft).Offset(0,
> 4).Column
> Range(Cells(FirstRow, FirstCol), Cells(FirstRow + 6, FirstCol + 2)).Select
> End If
>
> The IF portion determines where the range will be selected based on whether
> or not LeftRight is even/odd. Basically the "odd" time any of the "range
> selection buttons" are used the group of cells to the far left will be
> selected and merged where an even LeftRight will select the range a couple of
> cells to the right of the previous range and merge it. The problem I am
> having is that if I have 'Worksheets("Formula Sheet #" & FormSheet).Activate'
> statement in there I get an error. I've tried putting it in various
> locations and still get the same error. If I leave it out, the Ranges on the
> command button sheet get selected and merged in the same manner as I want the
> ranges on the Formula Sheet to be selected and merged.
>
> Also, I have tried moving the actual Range Selection statement to a location
> outside of the IF Then but get a 400 error. If anyone has any ideas on how
> to activate the formula sheet and select the ranges based on the LeftRight
> idea please let me know. All the variable are public so they can be used
> with any of the command buttons.
>
> Thanks,
>
> Chaz