Adding Tables Dynamically
Jan 2, 2008I am looking for a macro that will (most likely via button) create new table under an already existing one.
So for simple example, I have this ....
ADVERTISEMENT
Dynamically Adding Menu Items
Jan 22, 2010I have created a menu called "Temp" and a sub menu called "Temp1". When the user click on Temp1, I need to dynamically add some menu items under Temp1. Is this possible in Excel?
I need to do this on the right click menu. for example, along with "Cut", paste" etc menu's I need to add a Menu Item, Temp and under this menu, I need to add a SubMenu, Temp1. When the user click on the Temp1, I need to populate the child items.
Dynamically Adding Row Blocks Based On Cell Value
Apr 2, 2014I've managed to get coding that allows me to insert single rows dynamically based on a cell value. Example is if I enter a value in cell C3 the coding inserts the same number of rows below.
I've also created a simple macro that inserts a multiple row block under the active cell, complete with formatting, text and some merged cells. Example is I run the macro and it will insert a block of 3 rows, complete with formatting, borders, text and merged cells.
My challenge is to bring these two elements together. So enter a number in a single cell which will then insert the same number of row blocks, complete with formatting etc. I've added a file to sample what it is I'm trying to achieve. I can add the coding I already have as well.
Dynamic list sample.xlsx
Adding Events For Dynamically Created Controls
Oct 10, 2006I have somecode that scan column "A", and add dynamically "TextBox" Control according to some condition.
How to add event on dynamically created controls?
Private Sub btnSubmit_Click()
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
For Each rng In Selection
Dynamically Adding And Removing Controls On A Userform
Apr 5, 2007Is it possible to dynamically add or remove controls on an Excel userform using VBA? Doing this before the userform is displayed would be great.
View 9 Replies View RelatedDynamically Adding And Removing Checkbox - Linked Cells
Sep 15, 2014I have problems with dynamically adding and removing checkboxes. I have a form and there is a button to add more rows to the table. Every row includes a checkbox too.
So first i add a row, and then add a checkbox to a specified cell in that row.There is a button for removing rows as well (witch should removes checkboxes as well).
My problem is when i ad the checkbox the linked cell property only works for the first one.When i add the second row the linked cell of the firstly added checkbox changes to the one in the new row and the new checkbox has no linked cell. I am adding the stuffs as follows:
VB:
Private Sub addBtn_Click()
Dim y As Integer
y = findFunc("end") // Y define where To insert the New row
Cells(y, 11).EntireRow.Insert
Cells(8, 11).Copy
[Code] .....
Any way to add checkboxes dynamically.
Adding Rows To Pivot Tables
Aug 16, 2012Is it possible to add a row to the pivot table. All my fields run across the top of my excel sheet but i need a calculated field to show under the row under the total of each group. The values in this row must equal the value total in one of my fields. Is this possible ?
View 1 Replies View RelatedAdding & Naming Sheets "X" No Of Sheets Dynamically
Oct 28, 2009I have an Index Sheet where I would be typing the name of the Sheet and a Command button to execute the operation of Adding the Sheet with the name mentioned in the Column C..
View 14 Replies View RelatedExcel 2010 :: Creating Multiple Tables In VBA And Referring To Newly Created Tables?
Jul 1, 2013Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.
Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.
Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then
[Code].....
Adding Time: Adding Individual Cells Works, But SUM Doesn't
Mar 28, 2008I have a column of times: e.g. 10:03:00 and I would like to add them all up.
=A1+A2 works fine.
=sum(A1:A10) does not.
Adding Dim'd Variables: Ignoring Strings & Adding Number
Jan 13, 2007I've got a problem involving several Dim'd Variables needing to be added up, they're all Dim'd as Variants though as they can be either strings or numbers at any time.
I need a formula (VBA) to add them up (to add their actual numeric values - not just a "1" if they contain a number) and ignore them all together if they contain string values.
Adding Data With Userform: Check For Duplicate Before Adding
Feb 14, 2007I have a userform that I'm using to add data to a worksheet, with the following
Private Sub CommandButton1_Click()
Dim OutSH As Worksheet
Set OutSH = Sheets("Sheet1")
OutSH.Cells(nextrow2, 1).Value = Surname.Value
OutSH.cells(nextrow2,2).value = ID.value
OutSH.cells(nextrow2,3).value = Date.value
......
I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.
IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?
Dynamically Change Links?
May 21, 2014I have a link
='S:2014DomesticATL[ATL-BP Eval-2014-04.xls]Business Partner Evaluation'!F$13
Now i have to compile 3 years of data for 200 airports so changing the month and year for every airport would be a challenge. I can do Replace all and change the year and month but instead of that i was thinking if i could have the year and month on one row and connect the links to that cell so that whenever i drag the cell my link changes
im looking at
2014DomesticATL[ATL-BP Eval-",D2",.xls]Business Partner Evaluation'!F$13
where in D2 i will have 2014-04 . But this formula doesnt seem to work. Is there anyway i can do this ?
Dynamically Transpose Data
Jul 11, 2008I have a list of about 3,000 names and places of work, in two columns. What I want is to have the place of work in the first column, and then all the employees along that row.
There must be a simple solution to this, but I have been banging my head on my desk all morning trying to work it out. All the places of work are sorted, if that makes any difference.
Update Cells Dynamically
Dec 7, 2008I have a cell range of L3:N3 on Sheet 1 and on Sheet 2 in A1 i have the value of N3 of Sheet 1.
Now, if I move L3:N3 to eg. R10:T10 I want the value A1 ( Sheet 2 ) to be updated to T10 ( Sheet 1 )
Right now, it wont update dynamically, value of A1 stays at N3...
Want To Reference Another Cell Dynamically
Jul 13, 2009I basically am trying to just reference another cell (from a pivot table) from another sheet. ='SHEETNAME'!G5
But I Need the G to change... I basically need
='SHEETNAME'! + E29 + 5 to equal
='SHEETNAME'!G5 assuming G is in E29 but am not sure how to do this.
The other option is to use =GETPIVOTDATA("Sum of Amount_19",'SHEETNAME'!$A$3,"Label","Title") but I need "Sum of Amount_19" to change based on another cell, lets say F29.
Referencing Tabs Dynamically
Aug 24, 2009Is it possible to access other tabs on my spreedsheet dynamically? =COUNTIF(July!$F$4:$F$200,$H7) not dynamic
A1 = July. =COUNTIF(A1!$F$4:$F$200,$H7) dynamic. but how is this done in Excel?? Is it possible?
Dynamically Referencing Tab Names?
Nov 4, 2013I'm pulling data into some graphs from multiple sheets. The data I'm after is in the same place in each of the sheets, and the sheet name is varying only in that they refer to different dates, such as "01-11-13", 02-11-13 etc...
How would I go about dynamically picking up the sheet name. and referring to it?
For example if I have a column with all the different sheet names in it:
Col AA:
31-10-13
01-11-13
04-11-13
etc!
Dynamically Set Print Area?
Feb 12, 2014I'm trying to dynamically set the print area for a given worksheet. I use the code below and it works perfectly when it's executed upon opening the tab; however, the worksheet has filters which may lengthen/shorten the required print area.
Is it possible to execute the code below when the user selects print rather than when opening the tab?
[Code] ......
Add Numbers Dynamically Down A Column
Apr 20, 2009In the attached sample workbook I am trying to add the vendor number down the column H for each PO. I have copied a few of the numbers into the column, (in red), to show the desired result.
Basically, I will have a report daily with a variable number of suppliers, and variable PO's for each supplier. I need to add the vendor # to each row.
Grouping Of Rows Dynamically
Oct 21, 2009I am having a problem with a VBA macro I am trying to write. All I want to do is check all rows in a worksheet and group rows from the last bold row to the next bold row.
I seem to have the conditional logic correct, however, instead of grouping rows separately in each loop, it keeps joining them together.
Dynamically Merge Cells
Nov 19, 2009I'm am trying something that is still a little over my head...
Normally, I merge cells the easy way:
Dynamically Variable Ranges
Dec 31, 2006In my worksheet cell A1 represents a financial reporting period from 1 to 12. It also represents the number of columns I need to sum in order to arrive at the desired period totals in this income statement worksheet. How can I get Excel to automatically change my SUMPRODUCT function as the value in cell A1 is changed from 1 to 12?
Here is a sample cell formula:
=SUMPRODUCT(ISNUMBER(MATCH(GL1SHL.XLS!$A$2:$A$332, {"4620","5220"},0))*(GL1SHL.XLS!$CE$2:$CE$332)*(GL1SHL.XLS!$I$2:$T$332))
The above formula represents the totals for period 12 for General Ledger accounts 4620 and 5220.
GL1SHL.XLS! references the workbook that has the financial data exported from my G/L application.
$A$2:$A$332 is the column which contains the G/L account number to be matched.
$CE$2:$CE$332 references a cell that resolves to the value 1 or -1 so that expenses which are exported as positive values become negative.
$I$2:$T$332 represents the 12 columns and 332 rows that contain all the financial data necessary to calculate the income for period 12 with each row representing a different G/L account's 12 periods of financial data. I need a method to dynamically replace the 'T' in $I$2:$T$332 and resolve it based on the value in cell A1. So if A1=1 then 'T' becomes 'I' and if A1=2 then 'T' becomes 'J' etc.
I would like to do this as a macro substitution instead of having to make 12 worksheets, one for each period, or instead of having 12 nested loops.
Delete Last X Rows Dynamically
Dec 17, 2012Is it possible to run a macro that will delete the LAST X rows? It must be dynamic, and now be fixed to a specific row.
View 8 Replies View RelatedHow To Dynamically Format A Number
Jan 2, 2013I am trying to format a cell that contains the weekday number to display the name of the day but still keep its weekday number value. For Example, a cell contain the value of 2. This value is Monday as per Excel Weekday() value. I want to still keep the value as 2 but I want it displayed as Mon. If the value is 5 then I want it displayed as Thr.
Is there a way when creating a custom format display for a number that I can state 1="Sun"; 2="Mon"; 3="Tue"; 4="Wed"; 5="Thr"; 6="Fri"; 7="Sat".
How To Remove Duplicates Dynamically
Jan 27, 2014I have a problem to remove duplicates in Excel.
Ex:1
I have two columns Week No and Count
W1 50
w1 52
w2 25
w3 30
Here I can Remove weekly No but Later they will update more weeks. I want automated formula to solve this problem. Values I can add using sum-ifs formula.
Dynamically Hide Columns
May 15, 2007I am using drop-down lists in a cell to filter out some data in a spreadsheet. Works great, but I would like to hide certain columns depending on which item is selected in the drop-down list. I have written a macro to do this, but I cannot get the macro to run automatically every time there is a change in the drop-down list.
View 9 Replies View RelatedDynamically Chart Series
Sep 18, 2008I have calculated data (the column and row headings are calculated as well) similar to this for which I need to create an automatically updating chart.
******** language="JavaScript" ************************************************************************>Microsoft Excel - test.xlsm___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1 BalloonsFlagsCushionsPillowsStickers2Blue136013Red352104Green703245Yellow075056Orange558327White26073Sheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The chart should update if the data is filtered; so if a user doesn't want to see the 'orange' row they can filter the data accordingly. There is a finite number of rows and columns in the range but sometimes there will be less data. I have tried to convert the data to a table and that works quite well except the legend keys persist: so if there is no data for pillows and stickers the legends still appear and have to be removed manually. Also, there seems to be a bug in Excel 2007 whereby the removal of a chart linked to table data causes a persistent error message to appear.
I know that I need to created a named range for the data series using OFFSET, and I have also read that I need to refresh this formula using VBA but I am now very confused and none of my attempts quite work.
Dynamically Create Textbox
Oct 15, 2008I'm looking for some VB code that will enable me to dynamically create Textboxes for every value that I have in a range.
Example date in range B2:B5:
apple
banana
orange
pear
I would want the macro to create 4 Textboxes on the active worksheet, each one with the text contained in the range.
Dynamically Add A Hyperlink To A Shape In VBA
Dec 11, 2008I am trying to create a table of contents for a very long list of items on a sheet. I am using shapes on a seperate sheet to act as buttons, that go to the location on the correct sheet using a hyperlink. I found how to add a hyperlink using a macro, but I can't find how to create a hyperlink for the shape using a macro.
What is the code I would need to set a hyperlink to "Sheet1!A2:Sheet1!C2", with the a tooltip of "Some text"?
Here is how I create the shapes:
With myDocument.Shapes.AddShape(msoShapeRoundedRectangle, 10, IntVerticalOffset, 630, IntHeight)
.Fill.ForeColor.RGB = RGB(204, 255, 204)
.Line.ForeColor.RGB = RGB(0, 0, 0)
.TextFrame.Characters.Text = StrRowName
.TextFrame.HorizontalAlignment = xlHAlignLeft
.TextFrame.Characters.Font.ColorIndex = xlAutomatic
.TextFrame.Characters.Font.FontStyle = "Bold"
.Locked = True
End With