Userforms, switching sheets

[Charlie]

My userform, which has been using data from sheet1, will give the user an
option to edit a list on another sheet, sheet2. A command button will open
different form that pertains to cells on sheet2. I'll use code similar to
this, below. Do I need to somehow make sheet2 the 'active sheet'? I found
this code here in this help newgroup, but it refers to things like,
LastRow = Range("A2").End(xlDown).Row
....how will my code know which sheet? I could maybe put
LastRow = Worksheets("sheet2").Range("A2").End(xlDown).Row
??? ...but then I'd have to go though and always put this? Can I just make
sheet2 the active sheet, and then skip the Worksheet("") code?

thanks,
Charlie


Option Explicit
Private LastRow As Long
Private Sub UserForm_Initialize()
LastRow = Range("A2").End(xlDown).Row
rownumber = 2
End Sub

Private Sub cmdFirst_Click()
rownumber.Text = "2"
GetData
End Sub

Private Sub cmdPrev_Click()
Dim r As Long
If IsNumeric(rownumber.Text) Then
r = CLng(rownumber.Text)
r = r - 1
If r > 1 And r <= LastRow Then
rownumber.Text = FormatNumber(r, 0)
End If
End If
GetData
End Sub
Private Sub cmdNext_Click()
Dim r As Long
If IsNumeric(rownumber.Text) Then
r = rownumber
If r < LastRow Then
r = r + 1
Else
r = LastRow
End If
rownumber = r
GetData
End If
End Sub
Private Sub cmdLast_Click()
rownumber = LastRow
GetData
End Sub
Private Sub cmdSave_Click()

End Sub

Private Sub RowNumber_Change()
GetData
End Sub
Private Sub DisableSave()
cmdSave.Enabled = False
cmdClose.Enabled = False
End Sub
Private Function GetData()
' some code here to populate controls
End Function

 

[carlo]

Hi Charlie (again )

you could of course, but i wouldn't recommend it.
First of all it's harder to read...if someone else looks at your code
he can not be sure which sheet you mean.
Second of all, you will have trouble with debugging, if somewhere in
your code you change the sheet again and then forget to put it back to
the sheet.

You have different possibilities.

you could make a variable and set it to your worksheet:

Dim MyWorksheet as worksheet
set MyWorksheet = Worksheets("sheet1")

If you ever have to change the name of the worksheet, you would only
need to do it in one row.

then you can also use the with statement:

with worksheets("sheet1")
.range("A1") = "foo"
.cells(2,1) = .range("A1")
end with

with statement reduces the amount of code and makes your code more
readable.

hth

Carlo

 

[JLGWhiz]

Hi Charlie, If you use LastRow = Range("A63356").End(xlUp) etc. without
specifying a sheet then it will only apply to the active sheet. If you use
LastRow = Worksheets(2).Range("A63356").End(xlUP).Row then the last row
variable will only apply to Worksheets(2). Notice I use Worksheets(2) and
not Sheet 2. The Sheets collection includes Chart sheets. The Worksheets
collection includes only worksheets. So if Sheets(2) happened to be a Chart
sheet and you used the last row variable, it would error out.

By using the worksheet qualifier when you declare your LastRow variable,
your code will know that every time you use LastRow you mean Worksheets(2).
If you have need to use a last row variable on another sheet, you will have
to give it a different name like LastRow2 or LstRw or LR or anything but the
one already used.

For example:

lr1 = Worksheets(1).Cells(Rows.Count,1).End(xlUp).Row
lr2 = Worksheets(2).Cells(Rows.Count,2).End(xlUp).Row
lr3 = Worksheets(3).Cells(Rows.Count,3).End(xlUp).Row

Creates a variable for each worksheet in the active workbook.

Set myRng1 = Worksheets(1).Range("A1" & lr1)
Set myRng2 = Worksheets(2).Range("A2:B" & lr2)
Set myRng3 = Worksheets(3).Range("A1:C" & lr3)

Sets object variable ranges in three worksheets so that I can now create
commands for cells withing those ranges without having to activate the sheets.
Dim c As Range
For Each c In myRng2
If c = Worksheets(1).Range("F2") Then
c.Offset(0, 1). Copy Worksheets(3).Range("G" & lr3)
End If
Next
Uses three sheets without having to select or activate more than one
worksheet.

I tried to keep it simple but if I confused you I apologize.

 

[Charlie]

....this will make it a lot easy/better

with worksheets("sheet1")
.range("A1") = "foo"
.cells(2,1) = .range("A1")
end with

Especially after I asign them variables so I can easily change the name down
the road...
thanks again!

 

[Charlie]

....this will make it a lot easy/better

with worksheets("sheet1")
.range("A1") = "foo"
.cells(2,1) = .range("A1")
end with

Especially after I asign them variable name like you said so I can easily
change the name down the road...
thanks again!

 

[Charlie]

Ok, I named my worksheets, but when my code doesn't recognize the names once
I get out of the Private Sub UserForm_Initialize() code. Do I need to make
these name public? I don't understand everything I know about this
(obviously!)

....this is what I did:

Private Sub UserForm_Initialize()
Dim wsSchedules As Worksheet
Dim wsDates As Worksheet

Set wsSchedules = Worksheets("Sheet1")
Set wsDates = Worksheets("sheet2")


....and I can use

wsDates.Activate

and it's works, but once in a different sub() from another control it
doesn't recognize wsDates???

 

[carlo]

That is because WSDates only works in the sub you create it. This is
called scope of the variable.
As soon as the sub is finished, the memory of the variable will be
unassigned and you won't be
able to call it anymore.

You would need to make the variable public.

Another approach would be a Public sub which activates the sheet you
submit:

Public Sub ActivateSheet (WorksheetName as string)

On error goto EHandler
worksheets(WorksheetName).activate

Ehandler:
end function

hth

Carlo