Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
view data into userform
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 ****al shah. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
view data into userform
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. -- Regards, Tom Ogilvy "****al shah" wrote in message ... 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 ****al shah. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
view data into userform
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 ****al "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "****al shah" wrote in message ... 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 ****al shah. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
view data into userform
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. -- Regards, Tom Ogilvy "****al shah" wrote in message ... 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 ****al "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "****al shah" wrote in message ... 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 ****al shah. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
view data into userform
thanks Mr. Tom Ogilvy It's working. ****al "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "****al shah" wrote in message ... 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 ****al "Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "****al shah" wrote in message ... 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 ****al shah. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - data autofilter, then data sort how do you view all | Excel Discussion (Misc queries) | |||
View only UserForm? | Excel Programming | |||
MS Month View Control for use in XL Userform | Excel Programming | |||
View the data in a cell on a userform | Excel Programming | |||
View Row Data in UserForm / Edit | Excel Programming |