Deleting Keys From A Collection?

May 26, 2009

Is there a way to remove a value from a collection after I've added it.

I have simplified the code I'm using to highlight what I'm trying to do.

Basically I'm adding 10 keys to a collection and want to remove the last one. There is a reason for this.

View 3 Replies


ADVERTISEMENT

Short Cut Keys For Deleting A Particular Sheet

Oct 23, 2008

I want to know the Shortcut key for deleting a Particular sheet(s) from the excel workbook.

View 10 Replies View Related

Series Collection Vba

Jan 6, 2010

I trying to code a SeriesCollection in vba, below is the code I have:

' ActiveChart.Name = "Chart1"
ActiveChart.PlotArea.Select

ActiveChart.SeriesCollection(1).Name = "='Chart1'!$C$1"
ActiveChart.SeriesCollection(2).Name = "='Chart1'!$D$1"
ActiveChart.SeriesCollection(3).Name = "='Chart1'!$E$1"
ActiveChart.SeriesCollection(4).Name = "='Chart1'!$F$1"
ActiveChart.SeriesCollection(5).Name = "='Chart1'!$G$1"
ActiveChart.SeriesCollection(6).Name = "='Chart1'!$H$1"
ActiveChart.SeriesCollection(7).Name = "='Chart1'!$I$1"
ActiveChart.SeriesCollection(8).Name = "='Chart1'!$J$1"
ActiveChart.SeriesCollection(9).Name = "='Chart1'!$K$1"
ActiveChart.SeriesCollection(10).Name = "='Chart1'!$L$1"
ActiveChart.SeriesCollection(11).Name = "='Chart1'!$M$1"
ActiveChart.SeriesCollection(12).Name = "='Chart1'!$N$1"

However it it giving the error "Object denfined or Appication denfined"

View 9 Replies View Related

ComboBoxes Collection?

Jun 20, 2006

I am using the following code to determine whether a given range is the linked cell for a Combo Box by looping through the shapes collection.

Function LocateFormControl(OverRange As Range) As Shape
Dim objTemp As Shape
For Each objTemp In OverRange.Parent.Shapes
If Left(objTemp.name, 6) = "Drop D" Then
If WorksheetFunction.Substitute(objTemp.ControlFormat.linkedcell, "$", "") = WorksheetFunction.Substitute(OverRange.Address, "$", "") Then
Set LocateFormControl = objTemp
Exit Function
End If
End If
Next
Set LocateFormControl = Nothing
End Function

However, when I use this code on big worksheets with many other shape objects (such as Comments), the program runs very slowly. Is there a ComboBoxes Collection that I could use to avoid looping through all shapes on the worksheet, or is there a different, faster way to run this code?

View 2 Replies View Related

Using Keys To Run A Macro

Dec 15, 2009

Are we limited to just the Control key to run macros?
Can I use the Alt key?

View 2 Replies View Related

How To Remap Keys

Jun 23, 2007

Is there a way to remap keys in Excel? I would like to remap the F1 key.

View 2 Replies View Related

Alt Keys In 2007

Jul 28, 2008

I am having trouble with the 2003 Alt keys in 2007 ..

I have 2 computers (work & home)

The work computer works fine ... when i use same files on home computer some alt key features wont work?

Example Alt E S T .... (paste special formats)

Any ideas .... I've looked at sharing / protection ... cant find anything to fix it....

It cant be protection because the paste special formats works fine with a mouse or the new 2007 Alt key commands?

Just about ready to remove and re-install...

View 9 Replies View Related

Using VB To Press Certain Keys

Jan 15, 2009

Is it possible to create a script that will allow mimic the keypress of a keyboard?

For example, if I press the windows key and R, windows will bring up the run box and then I can type in C:Windows and press enter and it will open C:Windows.

Is it possible to have Excel do this?

This would mean I create a macro that has keypresses programmed in so I can run a command prompt and enter some details there then copy the data and paste it into Excel.

View 9 Replies View Related

Sort In More Than 3 Keys

Feb 18, 2009

I scratched this macro to autosort on more than three keys on a cell change...but is not working:

Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("A2:V1500")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met
' Do your thing here
rng.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("F2") _
, Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlAscending, Key4:=Range("C2"), Order4:=xlAscending, Key5:=Range("B2")
, Order5:=xlAscending, Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal, DataOption3:=xlSortNormal, DataOption4:=xlSortNormal, DataOption5:=xlSortNormal
End Sub

The error goes to Key 4 saying itīs a compilation error, argument with name doesnīt founded.

View 9 Replies View Related

Defining A Variable As New Collection

Dec 7, 2008

I have a piece of code I am using to important data from a text file. The code works fine but I am having a hard time understanding how defining a Variable "As New Collection" works. I have tried to research on numerous websites dedicated to excel but had no luck finding any further information.

I have executed the code line-by-line over-and-over and believe what the "new collection" does is it creates some type of external database that is hidden within excel (that's just my guess). I have attached a sample file and a text file with dummy data if needed to see how the code is working.

View 4 Replies View Related

Looping With/Through Object Collection

Oct 5, 2006

Im workin with a spreadsheet.. At the end of this script you see how i am putting "a" in R11C33, well I want to put the contents of my workbook C3 instead. I just dont know how to do it. After it does that I need it to move on to cell C4 and put it also in r11c33...

View 4 Replies View Related

Histogram Data Collection

Oct 27, 2006

I have a large table (20,000 plus entries) and I need a way to pull out data that matches criteria in buckets.

For example:
April data (column B) that is from 0 to 30 (column C)
April data (column B) that is from 31 to 45 (column C)
... 46 to 60
... 61 to 75
... 75 to infiniti

Then do it again for May, June, July, etc. I have used DCOUNTs to pull this type of data, but it's a bit cumbersome. Any other ways to pull this type of data from the table?

View 3 Replies View Related

Collection Of Objects - Add Method

Jan 28, 2007

I am attempting to create a double-entry accounting system and am having trouble with my Transaction class (clsTransaction). As each transaction will consist of at least two entries, I have included a Collection in clsTransaction that will accept Entry objects (clsEntry) as items. The Add method appears to go ahead without a hitch, but when I output the values from the Entries I get the same values for all items in the collection. Here is the code I've been working on.

The Transaction Class
clsTransaction - (Class Module)

Option Explicit

Dim mcolEntries As Collection
Dim msDescription As String

Private Sub Class_Initialize()
Set mcolEntries = New Collection
End Sub

Public Sub Add(eItem As clsEntry)
mcolEntries.Add eItem
End Sub........................

View 3 Replies View Related

Collection Of Userform Checkboxes?

May 2, 2007

I have a userform with three checkboxes on it. In the manuals I have read (and websites I am learning from) I have encountered articles that give me the impression that it is possible to create a collection of almost any object, and then quickly return properties about the collection as a whole. So, I am wondering, is it possible (or even appropriate) to group the checkboxes on this userform into a collection, and then, with a few lines of code, see if any of them are checked? If this is not possible, or efficient, is there another way to do it? I'll admit that three IF statements are not all that hard to enter, but I know the day is coming when I will make userforms with many more checkboxes in them.

View 4 Replies View Related

Static Collection Of Textboxes

May 31, 2007

I have a routine that creates a collection of textboxes to simplify adjusting their positions: ...

View 9 Replies View Related

Name Each Name Series Collection Of Chart

Feb 7, 2008

I hope this passes muster. Anyways, I wrote some VBA code to handle an embedded chart in my workbook. I named the chart "Data" but i'm getting some unexpected trouble from VBA. Last time I worked on this set of code, it ran fine. When I try to run it now, it is throwing up an error: "1004 Unable to set name property of series class" The .name function is not working correctly but everything up till that point is working correctly. I have 5 sets of data in this chart also ....

View 9 Replies View Related

Function Keys With Userforms

Sep 3, 2008

Does anyone know of any way at all to trap a function key while a userform is being shown, or will I need API for that?

View 13 Replies View Related

Sorting By Variable Keys

Oct 9, 2008

Sorting by variable keys
I have some code in a macro:

View 4 Replies View Related

Run Macro Via Shortcut Keys

Feb 10, 2010

The following macro works when I run using Alt+F8+Enter
The macro also works when it is linked to a button on the worksheet.
However, it does not work when I try to use the keyboard shortcut Ctrl+Shift+P.
I've tried adding the shortcut key code into the macro -- still doesn't work.
I've tried changing the shortcut key to a different letter

View 8 Replies View Related

Using Arrow Keys As Input VBA

Aug 5, 2013

I am trying to create the game of snake in VBA as practice, but I cannot find how to use the arrow keys as input. From what I have read it seems that it may not be possible, but how to change the value of a variable every time an arrow key is hit, post how to do so.

View 2 Replies View Related

Entering Times Without Using The : Or . Keys

Feb 13, 2009

i am a rowing coach and have to enter a lot of splits every day after practice and am hoping to find a way to do it without having to use the : or . keys.

so a split like 1min. 27.5sec, displayed as 1:27.5 could be entered as 1275.

i see that there are some questions already about this but seem to just work with H:M:S and time of day, i tried but wasn't able to use these for my purpose.

another fly in this ointment is that i also enter heart rates on the same sheet so in A1 it would be splits A2 heart rate, A3 split A4 HR.....

i do this for approximately 40 rowers each day with different workouts each day so each worksheet has splits and HR in different rows and columns.

View 9 Replies View Related

Replicate Arrow Keys

Dec 16, 2009

I understand the end(x1Down), but i think I am missing something. I just want to go to the next cell left, right or Up, Down from whatever cell is currently selected. How do I replicate a 1 cell move as an arrow key would do?

View 9 Replies View Related

Cannot Scroll With Arrow Keys

Jul 18, 2006

For some reason, when i use my arrow keys to move from cell to cell it now only scrolls my screen around....What did i do, and how do i change it back? I find it sad that i can write code and this stumped me.

View 3 Replies View Related

Arrow Keys To Move The Box

Jul 5, 2007

When every I click in a excel box and try to use my arrow keys to move the box example: A3 to A4. All it does is move the page up and down or across. Is there some type of setting on that is not letting me do this. It use to allow me to. Until another employee got on it. I can not start over becouse I have months of work on this book.

View 2 Replies View Related

Disable All Shortcut Keys

Nov 20, 2007

I want to disable ALL shortcut key functions in my program. (i.e. if a user presses Ctrl+S it will NOT save, Ctrl+O will NOT open file, etc).

Of course I need this enabled again in something like workbook close for users to have this ability in other workbooks.

View 9 Replies View Related

Separate Sheet Data Collection

Oct 31, 2009

I have a separate sheet(Coverage.jpg) that records some data which at this stage has to be entered manually.

The data comes from another sheet(officers.jpg), each row is 1 flight and the days are usually separated by a blank or grayed row.

I was wondering is there a formula that will collect the data automatically.

As you will see on Coverage.jpg it is broken down into Number of flights(per day), how many flights were covered by 2 or more officers and how many covered by 1 officer.. and then the graph generates off the data.

Is there a formula or something that will enter the per day data?

I have just added some false data to show you how some things get recorded.

View 14 Replies View Related

VBA - Are Named Ranges Part Of Collection?

May 27, 2014

Writing up some code, and I have a workbook with many named ranges. Question is simple...

Are named ranges a part of a collection object that I can use?

Looking to do something like:

[Code] .....

How to determine collection objects on my own in the future!

View 2 Replies View Related

Survey Collection - Up / Down Spin Buttons

Jul 10, 2014

I'm creating a spreadsheet to collect survey data and how I have it set up is this:

Question 1 is a simple yes/no/maybe question - it uses an ActiveX spin button that the user presses up/down to increase/decrease the total. It looks like this:

Private Sub Yes_SpinUp()
With Range("C3")
.Value = WorksheetFunction.Min(15000, .Value + 1)
End With
End Sub

Private Sub Yes_SpinDown()
With Range("C3")
.Value = WorksheetFunction.Max(0, .Value - 1)
End With
End Sub

Cell C3 is where I keep track of how many people answered yes

The button is called "yes" under the button properties

So basically, I have one privatesub for spinning the value up and down. It works fine and is easily copy-paste-able for other answers, it just takes up a lot of space. I'm wondering if there's code I can write to handle the up/down in one sub. If not, i'll stick with what I have.

View 6 Replies View Related

Add Series Collection From Dynamic Sheets

Jul 29, 2014

I've created one file: Mappe1_results.xlsm

This file ask in another file "Mappe1_ground.xlsx" for "B" and "N". Now one new sheet is created with the name "month-2014" and shows me which Category (B) is how often referred in one month. After that one chart is created for a better representation. This can be done for every month (which month is selected by the user over the button "Auswertung" in Sheet "Tabelle1").

Now I have a few problems / requirements:

1. If one user is using (maybe) Jan as his selected month, in the sheet "Auswertung" should be one duplicated chart of the Jan Chart., with no other (previous) series. Because Jan has no previous conditions (prev. year).
1.1 If one user is using another month (maybe Feb), in the sheet "Auswertung" should be one chart with both series of Jan and Feb and so one (for the other months). So that in "Auswertung" the chart is one comparison over the months. Only Feb/Mar/Apr/May/Jun/Jul/Aug/Sept/Oct/Nov/Dec have one prev. month.

For example: If one user write "Apr" into the inputbox and "Mar" is in the sheet existing, so in the chart of "Auswertung" should be April and March shown.

Some functions are set in my macros, but the problem is that the results are not equal between the sheet "Auswertung" and maybe "Jan-2014" or "Feb-2014". Because in "Auswertung" we need called all Categories (they can be found in "Referenz" - A).

All what I want is in "Auswertung" one chart with all present categories of "Referenz" - A and the series of the created sheets by the user.

2. The next problem is, every created sheet has one legend "Anzahl im ..." - this legend of every series should be shown in "Auswertung", too. So that we know which color is for which month, u know.

View 2 Replies View Related

Possible To Change The Index Numbers Within A Collection

Feb 1, 2010

I have created a userform that upon clicking a commandbutton adds a line of 4 textboxes. Everytime the user clicks the commandbutton a new line of textboxes is created. The 4 textboxes in each line are described by a class (hope I am using the terminology correctly). And each instance is saved in a collection. The problem is that I am giving the user the ability to insert a new row in between two existing rows.

When this happens, I need all of the index numbers to re-order so that they are consecutive from the top of the form to the bottom. Example: The user enters 6 rows of data and then realizes that they missed an item that needs to be inserted between rows 3 and 4. Currently the rows are indexed 1 to 6 with 1 being the row at the very top of the form and 6 being the row at the very bottom of the form. If I insert a row in between 3 and 4 I will end up with the index numbers going from top of the form to bottom (1,2,3,7,4,5,6). Is it possible to reorder the index numbers so that the inserted rows index number will be 4 and each row after that will go up by one?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved