view data into userform

[Guest]

hi
i have fileds like date,party_name,Item,Qty,rate,amt
where i use advance filter to filter requ. data & copy paste into new sheet.
useing code.

Sub Job()

Sheets("data").Select

Range("A4:F5000").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"A1:F2"), CopyToRange:=Range("BA4"), Unique:=False

Range("BA4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Report").Select
Range("A1").Select
ActiveSheet.Paste

Range("A4").Select

End Sub
IT works fine. But what i want is there can i put filter data into userform
and view them useing next and pervious button. where in user form we can not
make any change.
is any one will help me.

Thanks
Regards

Shital shah.

 

[Tom Ogilvy]

At the top of a new general module

Public rng as Range
Public cell as Range
Public Idex as Long
Sub ShowForm
Sheets("data").Select
set rng = Range("BA4").CurrentRegion.Columns(1).Cells
if rng.count <= 1 then exit sub
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
load Userform1
PopBoxes 1
Userform1.Show
End Sub

Public Function PopBoxes(iidex)
On Error goto ErrHandler
set cell = rng(iidex)
idex = iidex
with userform1
.Textbox1.Text = cell.Text
.Textbox2.Text = cell.Offset(0,1).Text
.Textbox3.Text = cell.Offset(0,2).Text
.Textbox4.Text = cell.Offset(0,3).Text
End With
PopBoxes = True
Exit Function
errHandler:
PopBoxes = False
End Function

in the userform module

Private CmdForward_Click()
if idex = rng.count then
exit sub
Else
PopBoxes idex + 1
End if
End Sub

Private CmdBackward_Click()
if idex = 1 then
exit sub
Else
PopBoxes idex - 1
End if
End Sub

Private CmdExit()
unload Userform1
End Sub

Create a userform1 with 4 textboxes name Textbox1 to 4
add 3 commandbutton. Name thenm
CmdForward (Caption Forward)
CmdBackward (Caption Backward)
CmdExit (Caption Exit)


Add or remove textboxes to fit your data.
You can embellish the form with more functionality

Code is untested and may contain typos.

 

[Guest]

Thanks Mr. Tom Ogilvy
It's working very Nice. But i want to ask one more thing, can we see only
selected filed. like there is "Rate Filed" in which i done want to inclued
in useform is this possible.

Rgards

shital

 

[Tom Ogilvy]

You have absolute control over which fields you display and in what Order.

This is what I showed you as an example,
.Textbox1.Text = cell.Text
.Textbox2.Text = cell.Offset(0,1).Text
.Textbox3.Text = cell.Offset(0,2).Text
.Textbox4.Text = cell.Offset(0,3).Text

so this showed values in columna A, B, C, D. But if I wanted D, A, G, M

.Textbox1.Text = cell.Offset.(0,3).Text
.Textbox2.Text = cell.Offset(0,0).Text
.Textbox3.Text = cell.Offset(0,6).Text
.Textbox4.Text = cell.Offset(0,12).Text

so you use your code to indicate what goes where.

 

[Guest]

thanks Mr. Tom Ogilvy
It's working.

Shital

You have absolute control over which fields you display and in what Order.

This is what I showed you as an example,
.Textbox1.Text = cell.Text
.Textbox2.Text = cell.Offset(0,1).Text
.Textbox3.Text = cell.Offset(0,2).Text
.Textbox4.Text = cell.Offset(0,3).Text

so this showed values in columna A, B, C, D. But if I wanted D, A, G, M

.Textbox1.Text = cell.Offset.(0,3).Text
.Textbox2.Text = cell.Offset(0,0).Text
.Textbox3.Text = cell.Offset(0,6).Text
.Textbox4.Text = cell.Offset(0,12).Text

so you use your code to indicate what goes where.