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.
I want to know the Shortcut key for deleting a Particular sheet(s) from the excel workbook.
View 10 Replies View RelatedI 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"
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?
Are we limited to just the Control key to run macros?
Can I use the Alt key?
Is there a way to remap keys in Excel? I would like to remap the F1 key.
View 2 Replies View RelatedI 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...
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.
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.
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.
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 RelatedI 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?
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........................
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 RelatedI have a routine that creates a collection of textboxes to simplify adjusting their positions: ...
View 9 Replies View RelatedI 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 RelatedDoes 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?
Sorting by variable keys
I have some code in a macro:
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
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 Relatedi 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.
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?
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 RelatedWhen 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 RelatedI 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.
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.
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!
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.
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.
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?
