Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #41   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,253
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,253
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Active cell counting in particular print page (one sheet having different print area) ananthmca2004 Excel Worksheet Functions 1 November 24th 05 12:29 PM
Why do I get a print error light trying to print an excel sheet ? SMC Excel Discussion (Misc queries) 2 November 5th 05 02:36 AM
Need Code To Print From Code Modules davidm Excel Programming 0 June 7th 05 06:11 AM
how to use Excel to generate alphabet serially Maxwell Excel Worksheet Functions 10 May 7th 05 03:37 PM
Help with Mr. Dave Peterson's Code for Consolidating Many Sheets to One Rashid Khan Excel Programming 2 July 31st 04 07:31 PM


All times are GMT +1. The time now is 07:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"