Home |
Search |
Today's Posts |
#41
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Mr. Peterson's Code.. Print serially from a Sheet
use JKP's NameManager addin to verify you dont have "double scope" range names. e.g. name ID exists on a Worksheet level AND at workbook level. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Dave Peterson wrote : Since the range named ID is on each of the sheets that needs to be printed (that's the one cell that gets changed for each value in the data|validation list), it should be a sheet level name. In this line: wks.Range("ID").Value = myCell.Value wks is the activesheet. So excel should be able to find that ID range (if you created it for that sheet). prkhan56 wrote: Hi Dave, What I meant is that if I have a global range name 'ID' then the code runs only for a single sheet PC Details Sheet in this case... but when I remove the global range name ID then even PC Details gives me run time. I think it has something to do with the Global and Worksheet Level Name...I removed the global range name 'ID' and tested it on other sheets then it works only for that particular sheet.... In other words... the code you suggested will not work unless there is a Global Range name 'ID' defined...and will work only for the Sheet to which the Global Range Name 'ID' is referred...Unfortunately I cannot have more than one Global Range Name 'ID' for other sheets Do you get what I am trying to say? Thanks Rashid Khan |
#42
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Mr. Peterson's Code.. Print serially from a Sheet
Hi Dave,
ID is not a range name on the sheet which needs to be printed...but ID and Data are on the sheet let's call it 'Main' Sheet for example. The sheet which is to be printed let's call it 'Form' is using the Global Name PCID to extract values. Because as mentioned in my previous post I cannot use sheet level name 'ID' to get the drop down box. I used JKP's NameManager addin to verify my range names. I have ID, Data, PrintArea as sheet level names only. Your macro only works if there is a range name defined as ID (Global)...otherwise it does not work... I have tested this with all relevant sheets...If there is a global range name ID then your macro works for that particular sheet only... I have tested with each sheet. Any suggestions? Rashid Khan |
#43
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Mr. Peterson's Code.. Print serially from a Sheet
I thought ID was the range that had the data validation cell--the one that you
change via the dropdown. I guess I don't understand why you can't use a sheet level name (ID) for each sheet. prkhan56 wrote: Hi Dave, ID is not a range name on the sheet which needs to be printed...but ID and Data are on the sheet let's call it 'Main' Sheet for example. The sheet which is to be printed let's call it 'Form' is using the Global Name PCID to extract values. Because as mentioned in my previous post I cannot use sheet level name 'ID' to get the drop down box. I used JKP's NameManager addin to verify my range names. I have ID, Data, PrintArea as sheet level names only. Your macro only works if there is a range name defined as ID (Global)...otherwise it does not work... I have tested this with all relevant sheets...If there is a global range name ID then your macro works for that particular sheet only... I have tested with each sheet. Any suggestions? Rashid Khan -- Dave Peterson |
#44
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Mr. Peterson's Code.. Print serially from a Sheet
Hi Dave,
Thanks a million...I have ID as sheet level name and it is working but a slight problem is occurring. May be you would be kind enough to rectify that too... Is it possible that I can type the code together with number for example EFG123 in the start box and EFG145... which would print the record from 123 to 145 for company EFG. As my numbers repeat also in some cases like ABC123 to ABC145... Now the macro prints the same number from all the companies... for eg When I put 123 in the start box and 145 in the end box.. then it will print ABC123 to ABC145, and then EFG123 to EFG145... and so on... I hope you get what I am trying to say. I have no words to express my thanks for all the time and help you have extended to me during all these days. Can the above problem be rectified? I thing the following needs to be modified : For Each myCell In myRng.Cells If IsNumeric(Mid(myCell.Value, 4, 3)) Then If StartVal <= Val(Mid(myCell.Value, 4, 3)) _ And EndVal = Val(Mid(myCell.Value, 4, 3)) Then wks.Range("ID").Value = myCell.Value Application.Calculate 'just in case wks.Range("PRINTAREA").PrintOut preview:=True the code should allow me to input the number eg ABC123 in the beginning box and ABC145 in the second input box... and then print accordingly Thanks a million once again. Rashid Khan |
#45
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Mr. Peterson's Code.. Print serially from a Sheet
Always 3 characters at the beginning...
If yes, then I'd ask for that prefix in another inputbox. You'll have to add this portion to your current code (I've lost track of what's current). dim myPfx as string ..... mypfx = inputbox(prompt:="what's the prefix") if trim(mypfx)="" then exit sub end if mypfx = left(mypfx & space(3),3) 'pad it with trailing spaces if required. Then the other part: For Each mycell In myRng.Cells If LCase(mycell.Value) Like LCase(mypf) & "*" Then If IsNumeric(Mid(mycell.Value, 4, 3)) Then If StartVal <= Val(Mid(mycell.Value, 4, 3)) _ And EndVal = Val(Mid(mycell.Value, 4, 3)) Then wks.Range("ID").Value = mycell.Value Application.Calculate 'just in case wks.Range("PRINTAREA").PrintOut preview:=True ..... End If 'one more end if to match up with that if End Sub prkhan56 wrote: Hi Dave, Thanks a million...I have ID as sheet level name and it is working but a slight problem is occurring. May be you would be kind enough to rectify that too... Is it possible that I can type the code together with number for example EFG123 in the start box and EFG145... which would print the record from 123 to 145 for company EFG. As my numbers repeat also in some cases like ABC123 to ABC145... Now the macro prints the same number from all the companies... for eg When I put 123 in the start box and 145 in the end box.. then it will print ABC123 to ABC145, and then EFG123 to EFG145... and so on... I hope you get what I am trying to say. I have no words to express my thanks for all the time and help you have extended to me during all these days. Can the above problem be rectified? I thing the following needs to be modified : For Each myCell In myRng.Cells If IsNumeric(Mid(myCell.Value, 4, 3)) Then If StartVal <= Val(Mid(myCell.Value, 4, 3)) _ And EndVal = Val(Mid(myCell.Value, 4, 3)) Then wks.Range("ID").Value = myCell.Value Application.Calculate 'just in case wks.Range("PRINTAREA").PrintOut preview:=True the code should allow me to input the number eg ABC123 in the beginning box and ABC145 in the second input box... and then print accordingly Thanks a million once again. Rashid Khan -- Dave Peterson |
#46
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Mr. Peterson's Code.. Print serially from a Sheet
Hi Dave,
This is the final version of your code which I have in my system and it works fine with a slight hitch. It takes about 45 seconds to display the first record...I have tested it with a sheet where there are no repetitions of numbers also...but still it takes about 35+ seconds to display the record. Can you guess what is making it to run so slow...May be you can give up a booster... Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Dim wks As Worksheet Dim StartVal As Long Dim EndVal As Long Dim TempVal As Long Dim iCtr As Long Dim myPfx As String StartVal = CLng(Application.InputBox(prompt:="Start with", _ Default:=1, Type:=1)) If StartVal = 0 Then Exit Sub End If EndVal = CLng(Application.InputBox(prompt:="End with", _ Default:=StartVal + 1, Type:=1)) If EndVal = 0 Then Exit Sub End If If EndVal < StartVal Then TempVal = StartVal StartVal = EndVal EndVal = TempVal End If Set wks = ActiveSheet Select Case LCase(wks.Name) Case Is = "pc details" Set myRng = Worksheets("pc").Range("data") Case Is = "printer form " Set myRng = Worksheets("printer").Range("data") Case Is = "monitor form" Set myRng = Worksheets("monitor").Range("data") Case Is = "switch form" Set myRng = Worksheets("switch").Range("data") Case Is = "router form" Set myRng = Worksheets("router").Range("data") Case Is = "firewall form" Set myRng = Worksheets("firewall").Range("data") Case Is = "modem form" Set myRng = Worksheets("modem").Range("data") Case Is = "scanner form" Set myRng = Worksheets("scanner").Range("data") Case Else: MsgBox "design error with worksheet: " & wks.Name Exit Sub End Select 'xxx???yyyyy myPfx = InputBox(prompt:="what's the prefix") If Trim(myPfx) = "" Then Exit Sub End If myPfx = Left(myPfx & Space(3), 3) 'pad it with trailing spaces if required. For Each myCell In myRng.Cells If LCase(myCell.Value) Like LCase(myPfx) & "*" Then If IsNumeric(Mid(myCell.Value, 4, 3)) Then If StartVal <= Val(Mid(myCell.Value, 4, 3)) _ And EndVal = Val(Mid(myCell.Value, 4, 3)) Then wks.Range("ID").Value = myCell.Value Application.Calculate 'just in case wks.Range("PRINTAREA").PrintOut preview:=True End If End If End If Next myCell End Sub Thanks for your help and all the support You were really helpful and caring. Rashid Khan |
#47
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Mr. Peterson's Code.. Print serially from a Sheet
I don't see anything that pops up that would cause that kind of delay in your
code. Do you have any event procedures in your workbook or worksheet? If you do, maybe adding application.enableevents = false 'right before this line For Each myCell In myRng.Cells 'more code Next myCell 'then application.enableevents = true end sub A second guess. That application.calculate line may not be necessary. If you have excel set for automatic calculation, you could try commenting that line. prkhan56 wrote: Hi Dave, This is the final version of your code which I have in my system and it works fine with a slight hitch. It takes about 45 seconds to display the first record...I have tested it with a sheet where there are no repetitions of numbers also...but still it takes about 35+ seconds to display the record. Can you guess what is making it to run so slow...May be you can give up a booster... Option Explicit Sub testme() Dim myRng As Range Dim myCell As Range Dim wks As Worksheet Dim StartVal As Long Dim EndVal As Long Dim TempVal As Long Dim iCtr As Long Dim myPfx As String StartVal = CLng(Application.InputBox(prompt:="Start with", _ Default:=1, Type:=1)) If StartVal = 0 Then Exit Sub End If EndVal = CLng(Application.InputBox(prompt:="End with", _ Default:=StartVal + 1, Type:=1)) If EndVal = 0 Then Exit Sub End If If EndVal < StartVal Then TempVal = StartVal StartVal = EndVal EndVal = TempVal End If Set wks = ActiveSheet Select Case LCase(wks.Name) Case Is = "pc details" Set myRng = Worksheets("pc").Range("data") Case Is = "printer form " Set myRng = Worksheets("printer").Range("data") Case Is = "monitor form" Set myRng = Worksheets("monitor").Range("data") Case Is = "switch form" Set myRng = Worksheets("switch").Range("data") Case Is = "router form" Set myRng = Worksheets("router").Range("data") Case Is = "firewall form" Set myRng = Worksheets("firewall").Range("data") Case Is = "modem form" Set myRng = Worksheets("modem").Range("data") Case Is = "scanner form" Set myRng = Worksheets("scanner").Range("data") Case Else: MsgBox "design error with worksheet: " & wks.Name Exit Sub End Select 'xxx???yyyyy myPfx = InputBox(prompt:="what's the prefix") If Trim(myPfx) = "" Then Exit Sub End If myPfx = Left(myPfx & Space(3), 3) 'pad it with trailing spaces if required. For Each myCell In myRng.Cells If LCase(myCell.Value) Like LCase(myPfx) & "*" Then If IsNumeric(Mid(myCell.Value, 4, 3)) Then If StartVal <= Val(Mid(myCell.Value, 4, 3)) _ And EndVal = Val(Mid(myCell.Value, 4, 3)) Then wks.Range("ID").Value = myCell.Value Application.Calculate 'just in case wks.Range("PRINTAREA").PrintOut preview:=True End If End If End If Next myCell End Sub Thanks for your help and all the support You were really helpful and caring. Rashid Khan -- Dave Peterson |
#48
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Mr. Peterson's Code.. Print serially from a Sheet
Hi Dave,
You are really wonderful...I just commented the Automatic Calculation line. Now I have it working. Thanks a bunch once again. Rashid Khan |
#49
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Mr. Peterson's Code.. Print serially from a Sheet
Glad you got it working!
prkhan56 wrote: Hi Dave, You are really wonderful...I just commented the Automatic Calculation line. Now I have it working. Thanks a bunch once again. Rashid Khan -- Dave Peterson |
#50
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Mr. Peterson's Code.. Print serially from a Sheet
after 3 weeks it is about time :) (i participated early in the thread, so I keep getting new message in thread alerts..) I admire your tenacity. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Dave Peterson wrote : Glad you got it working! prkhan56 wrote: Hi Dave, You are really wonderful...I just commented the Automatic Calculation line. Now I have it working. Thanks a bunch once again. Rashid Khan |
#51
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Mr. Peterson's Code.. Print serially from a Sheet
Sometimes, it's a good trait--other times, not so good <vbg.
keepITcool wrote: after 3 weeks it is about time :) (i participated early in the thread, so I keep getting new message in thread alerts..) I admire your tenacity. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Dave Peterson wrote : Glad you got it working! prkhan56 wrote: Hi Dave, You are really wonderful...I just commented the Automatic Calculation line. Now I have it working. Thanks a bunch once again. Rashid Khan -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Active cell counting in particular print page (one sheet having different print area) | Excel Worksheet Functions | |||
Why do I get a print error light trying to print an excel sheet ? | Excel Discussion (Misc queries) | |||
Need Code To Print From Code Modules | Excel Programming | |||
how to use Excel to generate alphabet serially | Excel Worksheet Functions | |||
Help with Mr. Dave Peterson's Code for Consolidating Many Sheets to One | Excel Programming |