Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting a Block of Cells to Define the Print Area
It used to be easy in the "old" days to capture a macro in which you
anchor the upper left most cell of the target range and then hold shift and press the right arrow, then the down arrow to highlight a range of cells. You could then make this the print area, change formatting, etc. I haven't learned the syntax for such in Excel's VB. It would be insightful to see how to write this in a macro (module) and also in the sheet where it could be executed with an active-x conrol. Thanks in advance for any examples, suggestions, etc. Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting a Block of Cells to Define the Print Area
Here is one way of doing it.
Private Sub CommandButton1_Click() intRow = 1: intCol = 1 Do While Cells(1, intCol) < "" intCol = intCol + 1 Loop Do While Cells(intRow, 1) < "" intRow = intRow + 1 Loop If intCol 1 Then Range("a1:" & Chr(intCol + 63) & intRow - 1).Select ActiveSheet.PageSetup.PrintArea = "$A$1:$" _ & Chr(intCol + 63) & "$" & intRow - 1 Range("A1").Select End If End Sub The code starts in cell A1. If you want to start in a different cell you will have to use ActiveCell.Address as the starting point. "Mark" wrote in message om... It used to be easy in the "old" days to capture a macro in which you anchor the upper left most cell of the target range and then hold shift and press the right arrow, then the down arrow to highlight a range of cells. You could then make this the print area, change formatting, etc. I haven't learned the syntax for such in Excel's VB. It would be insightful to see how to write this in a macro (module) and also in the sheet where it could be executed with an active-x conrol. Thanks in advance for any examples, suggestions, etc. Mark |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting a Block of Cells to Define the Print Area
set rng = Activesheets.Range("A1").CurrentRegion
Activesheet.PageSetup.PrintArea = rng.Address(external:=True) -- Regards, Tom Ogilvy "Mark" wrote in message om... It used to be easy in the "old" days to capture a macro in which you anchor the upper left most cell of the target range and then hold shift and press the right arrow, then the down arrow to highlight a range of cells. You could then make this the print area, change formatting, etc. I haven't learned the syntax for such in Excel's VB. It would be insightful to see how to write this in a macro (module) and also in the sheet where it could be executed with an active-x conrol. Thanks in advance for any examples, suggestions, etc. Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting a Block of Cells to Define the Print Area
hi
In the exemple behind, A1 is the most upper left cell of the range you want. This should work if you have the same type of data in all your range.... You can't have a blank column for exemple, or an error column...But else, it should be working. Range("A1:" & Range(Range("A1").End(xlDown).Address).End(xlToRig ht).Address).select |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting a Block of Cells to Define the Print Area
or another which does specifically what you describe.
set rng = Range(Range("B9"),Range("B9").End(xlDown).End(xlTo Right)) ActiveSheet.PageSetup.PrintArea = rng.Address(external:=True) -- Regards, Tom Ogilvy "Mark" wrote in message om... It used to be easy in the "old" days to capture a macro in which you anchor the upper left most cell of the target range and then hold shift and press the right arrow, then the down arrow to highlight a range of cells. You could then make this the print area, change formatting, etc. I haven't learned the syntax for such in Excel's VB. It would be insightful to see how to write this in a macro (module) and also in the sheet where it could be executed with an active-x conrol. Thanks in advance for any examples, suggestions, etc. Mark |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting a Block of Cells to Define the Print Area
Ran the code below once and it changed the region to bold.
I manually changed it back to regular. Then ran the code a second time and nothing happened, i.e. it didn't bold the text. Private Sub CommandButton1_Click() Dim xlApp As Object Dim xlWb As Object Dim xlWs As Object Dim xlRng As Object Set xlApp = Application Set xlWb = ActiveWorkbook Set xlWs = xlWb.Worksheets("sheet1") Set xlRng = ActiveSheet.Range("a1").CurrentRegion Selection.Font.Bold = True Set xlRng = Nothing Set xlWs = Nothing Set xlWb = Nothing Set xlApp = Nothing End Sub Why didn't it work the second time? Thanks, Mark Mark Roach Vice President Technical Resources The Oil & Gas Asset Clearinghouse Houston, Texas *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting a Block of Cells to Define the Print Area
you spend a lot of time declaring and seeting your variables, then work with Activesheet and selection. Your code does nothing with either the selection or activesheet, so it is a crap shoot at that point as to what will be changed. Private Sub CommandButton1_Click() Dim xlApp As Object Dim xlWb As Object Dim xlWs As Object Dim xlRng As Object Set xlApp = Application Set xlWb = xlApp.ActiveWorkbook Set xlWs = xlWb.Worksheets("sheet1") Set xlRng = xlWs.Range("a1").CurrentRegion xlRng.Font.Bold = True Set xlRng = Nothing Set xlWs = Nothing Set xlWb = Nothing Set xlApp = Nothing End Sub -- Regards, Tom Ogilvy Mark Roach wrote in message ... Ran the code below once and it changed the region to bold. I manually changed it back to regular. Then ran the code a second time and nothing happened, i.e. it didn't bold the text. Private Sub CommandButton1_Click() Dim xlApp As Object Dim xlWb As Object Dim xlWs As Object Dim xlRng As Object Set xlApp = Application Set xlWb = ActiveWorkbook Set xlWs = xlWb.Worksheets("sheet1") Set xlRng = ActiveSheet.Range("a1").CurrentRegion Selection.Font.Bold = True Set xlRng = Nothing Set xlWs = Nothing Set xlWb = Nothing Set xlApp = Nothing End Sub Why didn't it work the second time? Thanks, Mark Mark Roach Vice President Technical Resources The Oil & Gas Asset Clearinghouse Houston, Texas *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting a Block of Cells to Define the Print Area
Guess I'm missing a fundamental base, Tom. Was trying to execute one of
your suggestions (but rather than set the print range I was trying to control the bold font). Sounds like I made it more difficult than it could be. Mark Roach Vice President Technical Resources The Oil & Gas Asset Clearinghouse Houston, Texas *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting a Block of Cells to Define the Print Area
For anyone interested... here's the complete solution. This code is in
Sheet1 and is associated with an Active-X control button (CommandButton1). This code will toggle the font from bold to regular. Private Sub CommandButton1_Click() Set xlRng = ActiveCell.CurrentRegion If xlRng.Font.Bold = True Then xlRng.Font.Bold = False Else xlRng.Font.Bold = True End If End Sub Mark Roach Vice President Technical Resources The Oil & Gas Asset Clearinghouse Houston, Texas *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlighting a block of text within a cell | Excel Discussion (Misc queries) | |||
Set print area over locked cells | Excel Worksheet Functions | |||
Protect / Block selected worksheet area . | Excel Discussion (Misc queries) | |||
Block an area of sheet from Printing? | Excel Discussion (Misc queries) | |||
Block an area of sheet from Printing? | Setting up and Configuration of Excel |