Select Multiple Areas
Dec 23, 2006
I have a userform that has two comboboxs on it. When the user clicks and selects an item in combobox 1 the code will highlight an area of cells that I set before. When the user clicks and selects an item in the combobox2 I need a piece of code that will highlight a second area of cells while still keeping the first areas of cells highlighted.
How I have it set up now if the combobox1.text = "Monday" then the cells B6:H20 are selected. But when the combox2 click event fires and selects another range of cells the first range selected by the combobox1 event is unselected. Is there a way to keep both areas selected?
View 3 Replies
ADVERTISEMENT
Jun 23, 2014
In making my label printer, I will need to set the print area for multiple sheets based on a range generated in a cell. E.g. Sheet1 might need cells A1:P1200 set as print area, Sheet2 might need cells A1:G694 set as the print area, Sheet3 might not need to be printed, etc..
I have no problem generating a formula to state what the print area for each sheet should be, respectively, but passing that info into VBA has me really stumped. (Using indirect in the Page Setup menu doesn't work after the value has changed, it changes the value to a static reference, which is bizarre). I suspect I'd want to use the Sub Workbook_BeforePrint, but I'm not 100% on that. I will generally have 3 or more specific sheets selected using a button-triggered macro that gets a cell value of sheet names and selects those sheets.
View 4 Replies
View Related
Dec 6, 2006
My code is below. See how I have repeated the same code over and over for each row. Is there a shorter way to write this, perhaps a cleaner way. Im not an expert and I write what I know and most of that is long code that can be shortened.
If MyScreen.Area(7, 2, 7, 4) = "102" Then
Workbooks(2).Worksheets(1). Range("B" & row) = "Yes"
ElseIf MyScreen.Area(8, 2, 8, 4) = "102" Then
Workbooks(2).Worksheets(1).Range("B" & row) = "Yes"
ElseIf MyScreen.Area(9, 2, 9, 4) = "102" Then
Workbooks(2).Worksheets(1).Range("B" & row) = "Yes"
ElseIf MyScreen.Area(10, 2, 10, 4) = "102" Then
Workbooks(2).Worksheets(1).Range("B" & row) = "Yes"
ElseIf MyScreen.Area(11, 2, 11, 4) = "102" Then
Workbooks(2).Worksheets(1).Range("B" & row) = "Yes"
ElseIf MyScreen.Area(12, 2, 12, 4) = "102" Then .....................
View 3 Replies
View Related
Mar 20, 2008
I am trying to iterate through ranges (F2:G4) , (F5:G7) , ( F8:G11) , ... , lastrange
With each range I perform a simple median calculation.
The output of each calculation I would like to place in H2 , H3 , H4 , ... , lastcalculation
View 8 Replies
View Related
Oct 19, 2009
I have a situation which I hope is fixable. I have frozen panes above and to the left of cell F5, therefore all columns to the right of column E scroll left and right and all rows below row 4 scroll up and down as required. My problem is that I also need the whole top row to remain static all the time.
View 3 Replies
View Related
May 7, 2009
I am trying to write a vba code to change the zero values of the sheet to either 1, -1, or 0 according to certain rules. my sheet looks like this:
chcount=10chcount=10
0000
0000
0000
0000
1111
0000
1111
0000
0000
0000
0000
18111
0000
-1-1-1-1
0000
0000
0000
0000
-16-1-1-1
Basically I need the macro to select clusters of zeros in each of the columns with a label of 10 characters (this is an arbitrary number I chose when writing the macro), and if the values of the cells immediately above and below the cluster are the same, change the value of the zeros to the value of those cells, otherwise, the value of the custers remain as zero. So far I have the following code.
View 13 Replies
View Related
Sep 25, 2013
I would like a macro that select all cells based on multiple criteria. It's a big sheet, but I've made it smaller in the following example.
In row 1, there are 2 headers, in B1 (electricity) and C1 (gas) In column A, there are 3 houses, in A2, A6 and A10. The blocks of cells in between (e.g. B2:B5) have 2 headers, 'period' in B2, and 'account number' in B4. B3 and B5 have a list of options in them.
I want to select cells that meet certain criteria. Either electricity or gas, and either period or account number. So, if in cell D1 I choose electricity and in D2 period, I would like to select B3, B7 and B11. If I choose gas and account number, I wish to select C5, C9 and C13.
View 1 Replies
View Related
Jan 29, 2014
I've attached a sheet with details. I have references that I want to copy to a different cell but I want the reference to stay fixed AND allow the column to move.
Book1.xlsx
View 4 Replies
View Related
Apr 23, 2008
I'm trying to calculate the area of a Freeform shape.
Is this already stored somewhere e.g a shape property?
If not is there a macro available to do this?
View 12 Replies
View Related
Dec 5, 2009
I've got a spreadsheet that could have 1 of 3 possible print ranges:
PR1 = B2:K67
PR2 = B2:K132
PR3 = B2:K197
If there is a value greater than "0" in Cell B68, the print area would be PR2, but if there is a value greater than "0" in Cell B133, the print area would be PR3, otherwise it would be PR1.
These print ranges are such that the page break would would be below B67 and B132, but I'm concerned that I may need to set these page breaks in the Macro.
I'm not sure how to use the IF, THEN, ELSE, in the Macro to test the value of the cell and then set the print area.
View 3 Replies
View Related
Aug 26, 2009
can shut off the automatic calculation of a particluar worksheet or better yet, certain areas of a workbook, leaving the remainder of the workbook as Automatic?
For example, a workbook with Sheets 1,2,3 and 4. Can Excel 2007 be set for automatic calculations on the entire workbook except for Sheet3?
View 4 Replies
View Related
Jun 12, 2007
I have a table that lists staff according to where they are located, all adjusted using drop down lists, problem is that some locations have 5 staff and some up to 40, so when I set up a macro to print the area, those with 5 consultants look very small because the page area is so big. how can I adjust the print area dynamically so it only prints to 1 row below the last line with text?
View 9 Replies
View Related
Sep 19, 2008
'Get all the sheets
'y = 3
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "OM Files"
For J = 2 To Sheets.Count
With Sheets(J).UsedRange
x = Sheets(J).Name
If x "xxx" Then
.Resize(.Rows.Count).Copy _
Sheets(1).Range("A65536").End(xlUp)(3)
End If
End With
Next
Application.DisplayAlerts = False
View 9 Replies
View Related
Sep 24, 2008
When would the AREAS() Worksheet function be used? I've never seen it used and I've been Googling around to no avail.
View 9 Replies
View Related
Jun 10, 2009
I frequently receive workbooks containing up to 50 sheets and on most of them there is a selected area.
Is it possible to put a bit a code in the worbook when opening to clear all selected areas on all sheets.
View 9 Replies
View Related
Nov 27, 2006
I have an Excel sheet that I want to scroll up and down and I want an area in the upper right-hand corner to display static information. This information could change as more input into the sheet is performed, but it always must remain in the upper right-hand corner.
For example, let's assume the sheet has 200 rows to enter in a credit card application. There would be an area in the upper-right that would display information for some of the cells that were typed in. So as you type information, labels would display information from the form. I know that I can program the selection change event to re-position the box and labels, but if I scroll off the screen, the area does not stay there until I click on a cell on the page. I am using XL2003.
View 2 Replies
View Related
Jan 19, 2014
I have been conducting seasonal characteristics of the availability of certain fruits and vegetables in the region I am in (Italy), now I want to compare the data from my first seasonal characteristics region (Texas). My goal is to see trends, view characteristics of climate in that given month in an easy view.
I have data for both lists containing over 50 vegetables / fruits each. Along with different side notes that show factors of each month like climate, availability, flavor profiles, and cooking techniques.
Is it possible to put it into format showing a 12 month period, (jan, feb, mar,......) and then putting the type of fruit/vegetable to see trends? along with adding the side notes...
View 1 Replies
View Related
Feb 27, 2013
I have locked print areas on 7 work sheets. I have successfully printed them at 1 time. However it appears as if the print areas have to be set again before I print them again? What's up.
View 2 Replies
View Related
Jun 10, 2014
I have seven printareas as follows:
$G$8:$Q$40 - Landscape
$C$42:$M$108 - Portrait
$O$42:$Y$108 - Portrait
$C$110:$M$157 - Landscape
$C$159:$M$206 - Landscape
$O$110:$Y157 - Landscape
$O$159:$Y$206 - Landscape
As you can see, when I go to print preview, I will want the first and the last four to be formatted landscape and the second and third to be formatted portrait. This is all I want the macro to do. Then later when I go to print, I want to be able to print them all together. Is there a way to do this? Perhaps by letting the first printarea be on its on sheet, the second two on their own sheet, and the final four on their own sheet?
View 1 Replies
View Related
Mar 24, 2009
i need to pull data from one spread sheet and place it in a new spread sheet. and i am not that familiar with macros i am learning but i need to get this done.
View 13 Replies
View Related
Jul 30, 2006
What is the benifit of the worksheet function AREAS?
How can we use it with other functions?
View 14 Replies
View Related
Mar 31, 2007
I am attempting to set a range of cells (say c5:h25) that can be scrolled through horizontally whilst the rest of the sheet remains frozen.
I'm trying to set this so the scrollable range will automatically expand horizontally as user data is added to the last (h etc) column.
View 9 Replies
View Related
Mar 4, 2009
I have VBA code below. Currently the code allows me in column "N" to choose more than one item in a dropdown menu. I am trying to find a way to make column K and M do the same thing. I have tried a number of ways but to no avail.
View 2 Replies
View Related
May 3, 2013
How to do a ranking for productivity based on goals in six categories for my employees. I want to be able to rank them on these key categories and rank them based on the best average. For instance:
Employee #001 Ranked 1st in Sales per Hour, 3rd in Avg Transaction Amount, 2nd in Multiple Sales, 5th in Accessory Percentage
Employee #002 Ranked 2nd in Sales per Hour, 1st in Avg Transaction Amount, 1st in Multiple Sales, 4th in Accessory Percentage
I want to factor against the goals to determine each individual rank for each category and then rank based on the combination of them all.
I haven't gotten to all the additional conditional formatting but recall how to do this with ease, but the sheet in its basic format is present in the attachment below.
Employee Productivity.xls
View 6 Replies
View Related
Sep 27, 2013
I produce spreadsheets which is conditionally formatted to be both functional and aesthetically pleasing. The tables are separated by one line, and can easily be defined by VBA, I was wondering if it is be possible to export them as image files via vba? I know it can be done for graphs, but not sure about defined sections of the worksheet?
View 1 Replies
View Related
Oct 24, 2011
I have a column of the date of sales. (several hundred lines long) and each line has the name of the item sold. I need to complete a report showing how many sales of each item per date. Date of sale in one column and the name of the product in another.
View 3 Replies
View Related
Jul 1, 2008
I have a spreadsheet that lists values for different grid lines. On top of each set of data, I have labels each line "Line A", etc. I want to put in possibly a pull down menu at the top of the sheet that the user could use to jump to whichever line they were interested in viewing. For example, if the user needs data from Line L, they could select Line L from the drop down menu and the spreadsheet would jump down to that area.
View 7 Replies
View Related
Apr 21, 2009
i am making a spreadsheet for recording employee telesales activity each day.
administrators will update a shared workbook each day with the information
employee names are across the top and the activities are down the side (calls, CVs sent, interviews, meetings) each day has it's own block of data. on a friday I would like to run a macro to select these blocks and paste them into the first free row on a separate worksheet.
i have something similar working elsewhere which copies one column and pastes that into the last free column of a new sheet but i have no idea how to do it for rows going down a page. I have attached a file example to clarify.
View 6 Replies
View Related
Feb 24, 2008
I'm just stuck on a final thing......I would like the pivot table to collapse based on the outcome of a formula. For instance I've got a project that has A, B, C, D as component parts, these parts all have a percentage work completed. If let's say A is a 100% complete I would like to automatically collapse A, so it will just show the summary for A. Obviously this can be done by hand, but since there is quite a lot of data I would like to automate the proces.
View 8 Replies
View Related
Jan 19, 2009
i have 4 areas north east south and west, what is the forumla to enter into a cell that would automatically add the Salesperson when you know the area
Table is
Date, Area, (FORMULA), Amount
13/11/09, North, ( ), Amount
Basically there is a table with the salesperson per area
if i was writing it i would say its as follows
If Area = North then Mr Smith else if Area - South then Mr Jones etc etc
View 9 Replies
View Related
