Tick Cell Upon Selection. Restrict To Non Contiguous Ranges
Apr 2, 2008
I am trying to use excel to score a test. I want to tick a cell to do so. There is an excellente information about Tick Cell Upon Selection, posted here http://www.ozgrid.com/VBA/excel-checkbox-tick-cell.htm. Its range is limited to (A1:A100)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End Sub
How can I increase the range so it would target C1:C30, E1:E30 ( total of 17 columns)?.
View 3 Replies
ADVERTISEMENT
Jan 10, 2007
I was reading your Tick Cell Upon Selection article and it works great. I'm just wondering if there is a way to modify it so that they can only select one of the cells in a row.
For example, in this spreadsheet, the cells I have marked for "tick upon selection" are N8-Q8. In this worksheet, though, I only want the user to be able to select one of the four options. Is there a way to format the code so that they either cannot select another cell until they have deselected the first one, or the first one unchecks itself and the new click ticks that cell?
Example: Person selects N8. Person then selects P8. Can it not allow P8 to be selected (and give a warning message) or can it uncheck N8 when P8 is checked?
View 6 Replies
View Related
Nov 20, 2007
Can you have more than 1 worksheet change event on the same worksheet, if so, how do you name it to prevent the ambiguous name error. What code would I need to select a text value in colums e11:e15 based on the cell value in cell named STATE and place the selected value in cell e16. I have, thanks to this resource, one worksheet change event that selects a numeric value from any column E3,F3:F7 and places that value in cell C4. but the same code doesn't work for the new worksheet change event.
View 4 Replies
View Related
May 10, 2006
is it possible to have a userform activated as a result of a Worksheet_Change event on more than 2 cells. Currently I have the code below which will activate userforms if either a cell in Column G or a cell in Column T is changed.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("G:G", "T:T")) Is Nothing Then Exit Sub
If Intersect(Target, Range("G:G", "T:T")) = "" Then Exit Sub
If Not Intersect(Target, Range("G:G")) Is Nothing Then
GlngRow = Target.Row
Call Module3.ShowList1
End If
If Not Intersect(Target, Range("T:T")) Is Nothing Then
GlngRow = Target.Row
Call Module3.showlist4
End If
End Sub
What i need to do is activate another userform if a cell in Column AC is changed. I tried this code but got an error "Compile Error: Wrong number of arguments or invalid property assignment", which leads me to believe I can't add any more cells to the argument and it highlights the word Range in this line - If Intersect(Target, Range("G:G", "T:T", "AC:AC")) Is Nothing Then Exit Sub..........
View 6 Replies
View Related
Jul 20, 2007
I want to have a drop down list in a cell so that the value in the cell can be only selected from two columns of data. Additionally once the data from the first column has been selected I want to be able to limit the inputs the user can select from in the second column.
e.g. In cell C115 I want to have the value BDS05.
I want to be able to select the value BDS from a drop down list of values and once that value has been selected I want to be able select 05 from a list of values from 01 to 14
If I select BCS as the first value then I want the second set of values to be limited to 01 to 02 etc.
I have read about combo boxes and list boxes and I'm a bit confused about the best way to achieve this (or even if I can).
View 6 Replies
View Related
Aug 30, 2006
I am looking for a macro that would accept only one entry per selected ranges. It would be something like if there is one entry in range("b4:e4"), a message box would appear advising the user that only one entry is permitted in that range. It would give the option to delete the entry and to rekey the entry for that canditate.
I hope I am clear, if not let me know
1 entry in range("b4:e4"), per canditate
1 entry in range("f4:h4"), per canditate
1 entry in range("i4:l4") per canditate
1 entry in range ("m4:o4") per canditate
1 entry in range ("p4:r4") per canditate
1 entry in range ("s4:v4") per canditate
View 9 Replies
View Related
Sep 12, 2013
If I select a group of non-contiguous cells with control shift is there any way that I can shift the same selection over to the next column. For example, I have A4 and A8 selected. When I press shift and right arrow it doesn't work.
View 4 Replies
View Related
Sep 16, 2008
I get 40~100 page PDFs of purchase orders every week. Each page has one part number and its open order and forecast information. Some pages have a few line of information, some have many lines.
I convert this file into a txt file.
I then create a macro to import it as space delimited and format it.
What I need to do it get rid of the header/footer information on EVERY page.
When the txt file is created it doesn't have any page marks.
The header has "ABC Widgets" as the customer name and then 10 rows of unnecessary information. If I do a Find ALL for "ABC Widgets" and select all, it will delete that header line at every instance, but the leftover selected cells then start not-lining up (meaning I can't just hit "delete row" 10 times and have it pick the right row--the rows don't line up any more and each instance is -1 row offset.)
Is there any way to extend the selection of non-contiguous rows (starting with ABC Widgets) down by 10 so I can delete them?
View 9 Replies
View Related
Aug 9, 2006
I have a spreadsheet named PRODUCTS and in row A there are a bunch of product codes. I would like for the user to be able to use the CTRL and mouse to select all the products that they want and then once they push that button I would like it to paste the user selected cells into a sheet called Quote starting at A7.
View 4 Replies
View Related
Sep 4, 2009
I have two fields that do the same thing [preset selection vs custom entry], and I need to restrict one from holding any data if the other already does. One field is a drop-down menu, and the other is a decimal entry. It was easy to restrict the decimal from being entered if a drop-down selection has already been made - Data Validation prevents any entry unless the drop-down cell is blank. It's not so easy to restrict the drop-down, however, as it already uses List-type Data Validation to allow only values from a specified range.
Does anyone have any ideas for allowing the drop-down to function as normal if a specific other field or range is blank, but not allowing a selection to be made if that specific field or range already has data in it? I've attached a simplified example worksheet.
View 5 Replies
View Related
Feb 14, 2008
I have the following code which works but after the user gets an initial error says that they have to enter what is a ComboBox choice they get a second error when they press backspace to delete their entry. Is there a way to just give them the first error but not an error when they press backspace to erase their mistake in the form?
This checks the user input...
Private Sub Combobox5_Change()
If ComboBox5.ListIndex < 0 Then
MsgBox "Please Only Pick From The List. Use Admin Page to Add More to the List", vbCritical, "Error"
End If
End Sub ...........
View 9 Replies
View Related
May 22, 2008
I have a listbox that is populated via a macro. I can not use ColumnHeads so I populate line 1 in listbox as a header. The user can click on any line to open another box with more in depth data, I want to restrict the user from clicking on line 1, the header.
I have tried using listbox.listindex=1 whenever listbox.listindex=0 in mousedown and keydown but it fails to work although placing listbox.listindex=1 in the form initialisation sets line 2 as default when it opens.
View 3 Replies
View Related
Mar 23, 2009
I have a spreadsheet that I have developed at work to track sales related data. As part of the reporting for this data, it is sorted by date. I have been asked to capture some additional related data and due to the structure of the spreadsheet(that has been in use several months with no issues) I can not put the additional data in adjacent columns without doing a complete redesign of the format.
I can place the data several columns away.... The data "in the middle" is static and is used for other calculations and should not be sorted. Is there a way to sort both ranges of data WITHOUT disturbing the columns separating them and keep the proper relationship with the data?
See attached for a very simple example. In this data set the "original" data was in range A2:E11 the "New" data is in Range I2:M11. It would make life simple if I could do something like this (but it sorts everything in between)
View 2 Replies
View Related
Jun 21, 2007
how to Determine two uncontinnuous ranges are same? "Address" can not work here.
Sub Macro1()
Set Rng1 = Range("H1:H10,C1:C10,F1:F10")
Set Rng2 = Range("F1:F10,C1:C10,H1:H10")
Debug.Print Rng1.Address
Debug.Print Rng2.Address
If Rng1.Address = Rng2.Address Then
Debug.Print "yes"
Else
Debug.Print "no"
End If
End Sub
View 9 Replies
View Related
Sep 12, 2007
I want to count the number of cells with a “#n/a” in for a cell range which is non-continuous. For example my cell range is: “H5,J5,L5,N5,P5,R5,T5,V5". I’ve tried a few different things but I can’t get the function to work.
View 8 Replies
View Related
Feb 22, 2008
I'm creating a coversheet that shows the percentage of "yes" and "no" answers from several cells in other sheets of the book. My problem is that I'm trying to use a countif statement to do it, and the cells i'm counting aren't in a simple range. ex. A1:A10
In stead i'm trying to get something like:
=COUNTIF((Sheet2!A2,Sheet2!A10,Sheet2!A15),"yes")
View 2 Replies
View Related
Feb 28, 2008
I'm getting an error on Range("AB6:AS12,..."). The error is: "1004 Runtime error, Method 'Range' of object '_Global' failed". My code should copy the values from place A to B. Then clear the content of B. Heres my code:
Sub Ny_uke()
' copy cells
Range("B21:K50").Value = Range("AX21:BG50").Value
Range("B54:K83").Value = Range("AX54:BG83").Value
Range("B87:K116").Value = Range("AX87:BG116").Value
Range("B120:K149").Value = Range("AX120:BG149").Value
Range("B153:K182").Value = Range("AX153:BG182").Value
Range("B186:K215").Value = Range("AX186:BG215").Value
Range("B219:K248").Value = Range("AX219:BG248").Value
' clear content of cells
Range("AB6:AS12,B21:K50,B54:K83,B87:K116,B120:K149,B153:K182,B186:K215,B219:K248").Clear
End Sub
When the error occurs I can press 'Debug' and continue the script whiteout changing the code and the code will continue without any errors. If I place the line 'Range("...")' on top of the script no error occurs. I I split the 'Range("...").Clear' into several Range("AB6:AS12").Clear the problem solves, but this produces several unwanted lines of code.
View 7 Replies
View Related
Dec 28, 2009
What is the best way to declare non contiguos print ranges?
For instance I have the following
Sheets("Report").Range("C17:C33").Copy
But I want to add other print ranges like J5:K5
View 9 Replies
View Related
Nov 20, 2008
I have Workbooks("A") and Workbooks("B") open.
Workbooks("A") contains Sheets("Sheet1"). Range("mySource").
Workbooks("B") contains Sheets("Sheet1").Range("myTarget").
Both ranges have been created by joining multiple ranges, in this way:
Union(Range("C1:C13"), Range("K1:K2"), Range("K5:K9"), Range("K14"), Range("Q6"), _
Range("I18"), Range("B20:P20"), Range("B24"), Range("C26:E26"), Range("C29"), _
Range("B34:B40"), Range("B44:Q50")).Name = "mySource" 'or "myTarget"
So: both ranges contain the same number of cells with the same addresses, and they have been added in the same order. However, if now I try to pass all values from mySource to myTarget, in this way:
Workbooks("B").Sheets("Sheet1").Range("myTarget").Value = _
Workbooks("A").Sheets("Sheet1").Range("mySource").Value
the result is a complete mess. Only the first "subrange" of mySource ("C1:C13") is passed to myTarget, and pasted in each of its "subranges", sometimes by rows and sometimes by columns...
View 2 Replies
View Related
Jul 20, 2007
if there is a way to select a group of cells, select another group of cells and keep the previously group of cells selected.
Example: ...
View 6 Replies
View Related
May 8, 2008
I had some code working fine in office 2003:
Sub Replacing()
Dim rRange As Range
Dim lArea As Long
Dim Co As Byte
Dim NaCo As Byte
NaCo = 99
Set rRange = Range("B:C,E:F,H:I")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
Co = Choose(lArea, 1, 2, 3)
.Replace What:=Co, Replacement:=NaCo, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Next lArea
End With
End Sub
the problem is that it's not working for Office 2000
View 2 Replies
View Related
May 18, 2008
This time I want to replace:
number "99" in range("B:C") to number "1";
number "99" in range("E:F") to number "2";
number "99" in range("H:I") to number "3";
Sub Replacing()
Dim rRange As Range
Dim lArea As Long
Dim Co As Byte
Dim NaCo As Byte
NaCo = 99
Set rRange = Range("B:C,E:F,H:I")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
Co = Choose(lArea, 1, 2, 3)
.Replace What:=Co, Replacement:=NaCo, LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Next lArea
End With
End Sub
View 2 Replies
View Related
May 21, 2008
can anyone modify below macro to sort this data:
before sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-6-5-99-2-1-99-4-3
3-99-7-8-99-5-6-99-8-9
4-99-8-7-99-6-5-99-9-8
etc
after sort:
---A-B-C--D-E-F--G-H-I
1-99-5-6-99-1-2-99-3-4
2-99-5-6-99-1-2-99-3-4
3-99-7-8-99-5-6-99-8-9
4-99-7-8-99-5-6-99-8-9
etc
Sub SortNoncontiguousRanges()
Dim rRange As Range
Dim lArea As Long
'10 rows in columns B and C
'10 rows in columns E and F
'10 rows in columns H and I
Set rRange = Range("B1:C10,E1:F10,H1:I10")
With rRange
For lArea = 1 To .Areas.Count
With .Areas(lArea)
.Sort Key1:=.Cells(1, 1), _
Order1:=xlAscending, Header:=xlNo, Orientation:=xlLeftToRight
End With
Next lArea
End With
End Sub
View 9 Replies
View Related
Aug 2, 2014
I have tried:
Code:
With Sheet1
x = .[b7:i16,b19:i23,b27:i32].Value
But that only loads the first range (.[b7:i16]).
This does work:
Code:
.[b7:i16,b19:i23,b27:i32].Copy .[z1]: x = .[z1:ag21]: .[z1:ag21].Clear
I would rather avoid having to copy/paste, load the array, then clear. Is there a way to load the array directly?
View 2 Replies
View Related
Apr 29, 2008
trying to select multiple ranges of data at once using variables as my selection range criteria.
I.e., I want something similar to:
Range("A10:A30,B10:B30,E10:E30").Select
But would like to be able to perform the same selection using variables.
StartVar = 10, EndVar = 30
I'm sure it's just a matter of syntax, but I can't seem to get it right.
View 3 Replies
View Related
Mar 18, 2014
I use to have a macro that could copy formulas from non contiguous ranges and paste the values in the same cells. So for example, I could select a non contiguous range like B5:B10, E10:F12, and G1:G10. The macro would then copy and paste the values in those 3 ranges, so those 3 ranges would be values instead of formulas.
I think it worked by storing each range and then looping through each range to copy and paste special values.
View 2 Replies
View Related
Jan 24, 2012
I have a worksheet that has a few ranges and I need a printarea statement that looks like this:
Code:
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$26,$G$1:$L$9,$M$1:$P$16,$Q$1:$S$7"
The above works, but each time I generate this worksheet, the ranges for the last row of each area can be dynamic.
So, I tried something like this:
Code:
Sub setPrtArea()
'set the print area
lr1 = Range("F65536").End(xlUp).Row
rngA = Range("$A$1:$F$" & lr1)
lr2 = Range("L65536").End(xlUp).Row
rngB = Range("$G$1:$L$" & lr2)
lr3 = Range("P65536").End(xlUp).Row
rngC = Range("$M$1:$P$" & lr3)
lr4 = Range("S65536").End(xlUp).Row
rngD = Range("$Q$1:$S$" & lr4)
ActiveSheet.PageSetup.PrintArea = rngA & "," & rngB & "," & rngC & "," & rngD
But, it fails. I have looked through many topics on this subject, but nothing seems to fit my scenario. This will pretty much complete my current project if I get this figured out and can export these print areas to pdf without a bunch of blank pages as I get now with no print area set.
View 9 Replies
View Related
Jul 29, 2008
I transposed them into a separate sheet and used that for my lists. I have to share the file with macs.
My pc is on Vista running Excel 2003. The macs are OSX Panther (not Leopard) and running Excel 2003.
Solution (?): if I make the combobox lists programmatically, it will work on the macs?
The UserForm1 with 3 comboboxes:
The comboboxes are not dependent on each other.
For each combobox, I need to have 2 columns.
For each column, I need to transpose 2 x ranges (they are NOT contiguous)
My attempts have been embarassing and futile.
What it should be: ....
View 9 Replies
View Related
Feb 6, 2013
I have two pieces of code that perform virtually identical functions on different sets of data.Basically, the code deletes rows of data based on a selection in a listbox. One code works perfect, the other has an issue.
The Issue:
When I select a single row for deletion, or multiple contiguous rows, everything works fine. It is when multiple Non-Contiguous rows are selected that the problem occurs. I get a "Delete Method of Range Class" error.
The data for the code that doesn't work is in a defined TABLE, the date for the code that does work isn't. So I assume this is where the problem lies.
I converted the data for the code that did not work from a table to a range, and everything works fine. However, I would really prefer that this data remain a table. There is consistently going to be rows added and deleted, and a table will take those adjustments into account, whereas a named range shrinks each time I delete a row, and doesn't grow as I add rows.Here is the code that works:
VB:
'Delete Selected Offers
Else
For r = 0 To ListBox1.ListCount - 1 [code].....
View 6 Replies
View Related
Dec 23, 2009
I am trying to call a function that calculates forecast error (wMAPE). This function needs to be able to handle passing in non-contiguous ranges. I can't seem to figure out how to do that.
Function wMAPE(Forecasts As Range, Actuals As Range, Weights As Range) As Variant
Dim Denominator As Double
Dim Numerator As Double
Dim i As Long
Dim Fcst As Variant
Dim Act As Variant
Dim Wt As Variant
If Forecasts.Cells.Count Actuals.Cells.Count Then MsgBox ("Error: Arrays not same size")
If Forecasts.Cells.Count Weights.Cells.Count Then MsgBox ("Error: Arrays not same size")
Denominator = 0............
View 9 Replies
View Related
