Dynamically Created Range Names In A Macro

Apr 1, 2008

I have 100s of name ranges in my spreadsheet. I am using a series of drop down boxes for the user to select the info that they want to see. Based on the dropdown box, the macro "dynamically generates" a range name from which to pull the info.

For instance, if they Select Manager = Terry, Analyst 1, and Month = Jan, then the range name would be JanTA1

The if statement would dynamically create this name by concatenating the info

If manager = Terry
mnger = "TA"
if analyst = 1
anlyst = 1
if month = Jan
mnth = "Jan"

rangename = mnth & mnger & anlyst

So, I am dynamically creating the range name based on user selections...then to display it, I want to set the display cell = to the value

Range("display cell").Value = Range(rangename).Value

The problem is, I am getting an object error every time I try to refer to a range this way.

If I type:
Range("display cell").Value = Range("JanTA1").Value, it works

View 9 Replies


Range Names Created From String Variables

Aug 30, 2006

I work with data that varies in row numbers but is consisten in column width. I am trying to write code that will create a named range for the data but be flexible to expand or contract based on the amount of data that is pulled in. Below is the

Const lngLastPossRow As Long = 65536
Dim strDataRng As String
strDataRng = ActiveSheet.Name & "!R4C1:R" & Range("a" & lngLastPossRow).End(xlUp).Row & "C17"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveWorkbook.Names.Add Name:=("Data"), RefersToR1C1:= _

While the result creates a named range called "Data" , it does not allow me to reference and data in any formulas ( sumif's, etc). Can someone tell me what I am missing. It appears to be returning the wrong data type (string as opposed to range values).

View 6 Replies View Related

Dynamically Created Tab As Table Name In SQL Query

Jun 8, 2014

I am having a excel vba code that copies tab from different excel file and then i want to use in sql query. It is always giving me error saying that "The Microsoft Access database engine could not find the object 'rahul'. Make sure the object exists and that you spell its name and the path name correctly. If 'rahul' is not a local object, check your network connection or contact the server administrator."

Though, when i rerun the macro after reopening the excel file, it is working fine. Seems like tabs dynamically created cant be used. Is their any way to get it done at runtime? I want to delete new created tab once data from new tab is inserted to required tab.

my macro calls first copyAllSheets(filename) and then writeSheet().


Private Function copyAllSheets(ByVal fname)
Dim destSheetCount As Integer
Const FUNCNAME = "copyAllSheets"
On Error GoTo ErrorHandler
Set wb = Workbooks.Open(fname)
destSheetCount = ThisWorkbook.Worksheets.Count + 1
wb.Worksheets("Buyer").Copy after:=ThisWorkbook.Sheets(1)
ThisWorkbook.Worksheets(destSheetCount).Name = "impBuyer"


This is part of code so probably it wont be easy to understand the flow. but mainly i want to know how can i have newly created tab as table_name.

View 7 Replies View Related

Looping Through Dynamically Created Command Buttons?

Apr 27, 2014

Code to create buttons on a userform. I have adapted his code to do what I want. The code he gave me was:


In the Class Modules:

[Code] ........

All buttons when created are coloured yellow. When a button is clicked, it turns blue (to indicate that the button was clicked. However, my problem is that when another button is clicked, it also turns blue as coded. This means that both are showing blue colour. I want all other buttons to turn yellow and only the button clicked should turn blue.

View 5 Replies View Related

Reference Dynamically Created Label From A Class

Apr 27, 2014

The following code dynamically creates a number of textboxes within a frame with a label right next to it. There are two classes.

[Code] ......

The two classes look like this:

[Code] .....

This works just fine to create the dynamic series of textboxes and corrosponding labels. I am having trouble with the reference between them. for example (within the class module):

[Code] .....

As shown, the reference to the corresponding lable from within the class doesn't work. How to reference the correct corresponding label.

View 13 Replies View Related

Calling Content Of Dynamically Created Textboxes?

Jun 3, 2014

Basically, I am taking user input to determine how many textboxes to create during runtime, and want to call the contents of those textboxes after the user has put something in them.

View 1 Replies View Related

Adding Events For Dynamically Created Controls

Oct 10, 2006

I 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(Selection, Selection.End(xlDown)).Select
For Each rng In Selection

View 9 Replies View Related

Change Caption Of Dynamically Created Label

Jun 26, 2007

For a form that dynamically creates a series of text boxes and labels, does anyone know how to change the caption of a dynamically created label to display the value of a cell in a worksheet? The cell in the worksheet changes according to input on the text boxes. In the form's code, I have:

Dim newLblFreq As MSForms.Label
Set newLblFreq = Me.Controls.Add("Forms.Label.1", "lblfreq" & i, True)
newLblFreq.Caption = Range("V" & i).Text & " Hz"

But once the label is created, the caption won't change, even if Range("V" & i) changes...............

View 6 Replies View Related

Can Create Data Validation List Of Names Created In Name Box Or Sheet Tab Names

May 7, 2012

Can I create data validation list of the names created in the name box or of the sheet tab names?

View 5 Replies View Related

Using Class Module For Handling Events Of Dynamically Created Controls

Mar 4, 2009

I have a userform with a single blank multipage. At runtime additional pages are added, the number of pages depends on input from another userform. Six frames, containing labels, textboxes and comboboxes are then added to each page.

I need to be able to use the textbox and combobox change events of these dynamically created controls to perform lookups and calculations. Although I can name the controls at the time they are created, it is not feasible to write code specifically for each control (I can have over 1,600 text boxes distributed over 9 pages, for example).

Having searched for some time on how to achieve this I believe using a class module is the way to go. However, how to use a class module is just not sinking in I'm afraid.

HTML Private WithEvents mpTextBox As MSForms.TextBox
Private WithEvents mpComboBox As MSForms.ComboBox

Private Sub Class_Initialize()
Set mpTextBox = MSForms.TextBox
Set mpComboBox = MSForms.ComboBox
End Sub

Private Sub mpComboBox_Change()
MsgBox "ComboBox value has been changed."
End Sub...........................................

View 2 Replies View Related

Create Click Events For Command Buttons Created Dynamically At Runtime

Apr 2, 2013

I have a userform that has one combobox at the top created manually. When the userform is opened, the user select an option in the combobox (these options are taken from a range on 1 worksheet). From the selection of the combobox, I use the comboxbox's change event to create and display 5 columns of textboxes and 2 columns of command buttons on the userform.

The number of rows of textboxes created depend on the option selected from the combobox since each option links to a different range of cells. Each of the 5 textboxes in each are set to be ".enabled = False" and display text as per the cell values within a range on another worksheet. 2 Columns of command buttons are created at the end of each row of textboxes - 1 is enabled and the other is not.

The creation of the textboxes and command buttons works as required. However, I am having problems with setting click events for each command buttons. When the 1st column of Command buttons are created, I need the click events to be created and filled out with 2 actions:

1. Enable all textboxes in the same row as the command button

2. Enable the other command button in the same row.

Here is the code I have so far that creates the textboxes and command buttons.

Each of the 5 textboxes and 2 command buttons have a unique name so the 1st row will have textbox and command button names of cTxtA1, cTxtB1, cTxtC1, cTxtD1, cTxtE1, CmdAmend1 and CmdConfirm1. The 2nd row will have the same names but with 2 on the end and so on. The bold sections is the code for the creation of the command buttons that I want click events for.


Private Sub CboTeamSelect_Change()
Application.ScreenUpdating = False
If CboGroupSelect.Value = "" Then Exit Sub
Dim cTxtA As Control, cTxtB As Control, cTxtC As Control, cTxtD As Control, cTxtE As Control
Dim CmdAmend As Control, CmdConfirm As Control
Dim iNum As Integer
Dim TxtTop As Long


View 2 Replies View Related

Dynamically Referencing Tab Names?

Nov 4, 2013

I'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:

View 11 Replies View Related

Declaring Variable Names Dynamically

Jun 11, 2007

Is it possible to declare n variable names dynamically in a macro? For example: Cell A2 in Sheet1 contains the number of variables to be considered in the macro (n). I would like all the variables to take names in the macro from node1 to noden using the dim statement. I tried running a for loop:

View 14 Replies View Related

Assign Names Dynamically Based On Cell Content

Jul 10, 2009

I do have a data set from A1:C8 as follows:-
Brand Name(A1)Sales Value(B1)Margin(C1)Levi's100002000Arrow150002500Allen Solly120001300Hanes80001000John Miller250004500Disney280003200Ray Ban180002100

The above brands are categorized as follows:-
Brand NameProposed CategoryLevi'sConsignmentArrowBrandAllen SollyBrandHanesBrandJohn MillerPrivate LabelDisneyBrandRay BanConcessionaire

I would like to put formula from D1:D8 in such a way so that the Proposed categories are extracted automatically through formulae. This will help in getting the contribution calculated from different categories.
Brand Name(A1)Sales Value(B1)Margin(C1)Category(Desired Result)(D1)Levi's100002000ConsignmentArrow150002500BrandAllen Solly120001300BrandHanes80001000BrandJohn Miller250004500Private LabelDisney280003200BrandRay Ban180002100Concessionaire

View 9 Replies View Related

Newly Created Tab Names Placed As Hyperlink On Content Sheet

May 11, 2006

I attach an example worksheet with the code I have thus far.

In my workbook I have other sheets, one of which changes and updates a specific one each time a new client's data is entered on said other sheet. Because I want to save the specific client's data and not lose it when another client's stuff is entered on this other sheet, I copy the sheet where the data is summarised (I called this sheet "Sheet to Copy From") to a newly inserted sheet and use Paste Special, Values Only to change all functions /f ormulae / Links ect to values.

I then change the name of the sheet to the name of the client.

I then use this sheet name / cell value to polulate a range on another sheet (Next Empty Cell) as a Hyperlink to the newly created sheet above. This sheet I called "Table of Contents".

how to code the hyperlink. Using Macro Recorder uses the specific case's names, but the Tab name to be used as Hyperlink value will always be the name of a new client,

View 8 Replies View Related

Excel 2007 :: Picture Created With Unique Names - Adding Comments To Cell

Oct 23, 2012

Excel 2007

I have a few dozen pictures created when a macro runs. They all have unique names. I'd like to add comments to cells, where the cell.value decides which picture to pull. All the examples I've found online show how to do this if you have pictures saved on your hard drive by referencing the file path "c://mydocs/...blahblah/"

Is there a way to reference the pictures I've created/named with my macro?

Here's the snippet of code that creates the pictures and names them:

For i = 2 To Application.CountA(Sheets("Allocation").Rows(1))
Set rInput = Sheets("Allocation").Range(Cells(1, i), Cells(10, i))
sPicName = "_" & Sheets("Allocation").Cells(1, i) & "_"
sSheet = Sheets("Allocation").Cells(3, i)
dDate = Sheets("Allocation").Cells(5, i)

[Code] ......

Here are some examples that are close to what I'm looking for.

VBA Popup Pictures - 1108 - Learn Excel from MrExcel Podcast - YouTube
VBA Express : Excel - Add pictures that float like comments.

View 4 Replies View Related

Rename Range Names Via Macro

Sep 7, 2007

I am trying to automate the updating of range names in a spreadsheet.

On Sheet 1 Col 1 I have the names of all the existing range names.

In col 2 the new range names.

On sheet 2 the data referenced via range names.

I am getting stuck renaming the old range to the new name.

Sub Test()
Dim sName As String, nName As String

Do While ActiveCell ""
sName = ActiveCell.Value
'MsgBox sName
Application.Goto Reference:=sName
ActiveCell.Offset(0, 1).Activate
nName = ActiveCell.Value
ActiveCell.Name = "nName"
ActiveCell.Offset(1, -1).Activate
End Sub

View 9 Replies View Related

Delete All Range Names Macro Code

May 13, 2008

I have written VBA code which is operated every month. This code deletes several range names in a target spreadsheet, performs other actions - including extracting certain areas to different spreadsheets and then closes the spreadsheet without saving.

The range names in the target spreadsheet are required for ongoing use, so can not be deleted permanently.

Is there any code that deletes all range names in one go? At present my code includes the results of recording a macro wherein I delete each range name in turn - creating script over 100 lines long.

View 3 Replies View Related

Get The Range Address Of A Dynamically Named Range That Refers To A Formula In VBA

Aug 10, 2008

I have a named range that expands and contracts based upon the amount of data that is in some column. Call it AllData_UsedRange.

I have another named range that actually refers to a range. Call it AllData.

Column A
Row2 56
Row3 44
Row4 65

AllData is a named range that refers to the range A2:A65536
AllData_UsedRange refers to A2:A4 by way of this formula.

How to I obtain an address of AllData_UsedRange in VBA code?

These do not work...

View 9 Replies View Related

Return VBA Created Range

Nov 24, 2008

I am trying to create a function in VBA that will return a range, but I don't want the range to point to cells on a spreadsheet. I want the range to return a vba created range. Is this possible or impossible? Is a variable of type range required to point to cells on a spreadsheet?

View 14 Replies View Related

Dynamically Generated Range

Aug 14, 2007

I am have some code that takes values from a range (sourceAll) defined on my worksheet as:

Set sourceAll = Workbooks(pathnm).Worksheets(m).Range("A1", "S34")

I'd now like to be able for the range to be generated dynamicaly, but my problem is that the far right column (S) does not always have a value for every entry. Is there a way of using a column that does have a value for every entry, say column B, but then extending the range accross to S? If possible I'd like to avaoid having to move one of the columns with values for every entry. Auto Merged Post;I was looking at some of the suggested threads and found some code that I worked into my own

Set countRow = Workbooks(pathnm).Worksheets(m).Range("B1250").End(xlUp)
Set sourceAll = Workbooks(pathnm).Worksheets(m).Range("A1", countRow)
sourceAll = sourceAll.Resize(sourceAll.Rows.Count, sourceAll.Columns.Count + 17)

now this seems to work mostly, but it has deleted everything that was in column B.

View 2 Replies View Related

Dynamically Reference Range Of Cells?

Jun 27, 2012

I have a 'receipt' worksheet with about 500+ lines of data, here is an example of what could be in cell A166: 1.1 NET_AMOUNT 742,523,253.83

In another worksheet, is there a way to look at the whole worksheet for column A in receipt to say:
if 1.1 is true, capture text to the right of 'NET_AMOUNT' then format using =Dollar?

View 9 Replies View Related

How To Define A Cell To Be A Range Dynamically

Feb 14, 2013

The way I have try to define lastcell is wrong. But I don't know how to do it.

Sub createUniqueRecordID_2()
' fill using CTRL + ENTER
Dim lastcell
Sheets("projects list").Select


View 1 Replies View Related

Dynamically Change The End Value Of A Range In A Function

Jul 20, 2006

I am using the STDEV function on a range of values: STDEV($I$26:I2545). My starting cell is constant and I would like to make my ending cell variable based on a value I enter in a master cell (A1). The value I enter in the master cell is multiplied by 252 the product would equal the row number I would like to stop at. So if I enter 1 the formula will be STDEV($I$26:I252). I tried: STDEV($I$26"I"&(A1*252))) and other such variations to no avail

View 5 Replies View Related

Change Chart Range Dynamically

Sep 24, 2006

I maintain Production data in an Excel Sheet and have the code which will create Chart for each Employee. Sample Sheet Attached. However, I want the Chart Range to be updated in a Single Chart and exported to a .Gif File.

View 2 Replies View Related

Change Size Of Range Dynamically

Aug 10, 2007

Need assistance with this code provided by SHG. See attached sample. I need to create a range called DaysOff so that this function will work. As it is the function is perfect; though the accuracy if the result is dependent on the exact range, thus I need this named range to be dynamic. I have tried but it does not seem to work correctly. The named range should refer to the list of names between 1-10. The range should dynamically include any names that are listed.

Public DaysOff As Range
Function datNext(datLast As Date, DaysOff As Range) As Date
Dim datBeg As Date, datEnd As Date
Dim iLastPd As Integer
Dim iDay As Long, nDay As Long
Dim iYr As Integer, iMo As Integer
iYr = Year(datLast)
iMo = Month(datLast)
datBeg = DateSerial(iYr, iMo + 1, 1)
datEnd = DateSerial(iYr, iMo + 2, 0)
iLastPd = Pd(datLast)
nDay = DaysOff.Rows.Count

View 2 Replies View Related

Dynamically Expand Range To Create Chart

Jan 21, 2009

See the attached example. I have data listed as shown, but need the chart to automatically update whenever a new sales figure is added to the next month. I know you have to use the OFFSET to create dynamic ranges but I can't get it to work horizontally, only if my data is laid out vertically.

View 2 Replies View Related

Referencing A Named Range Or Sheet Dynamically?

Apr 19, 2013

I have a challenge I'm trying to surmount. I have an excel sheet in which I want to be able to select Zone codes from a drop down, click on a link "search" which takes me to a place in the document that has been named according to the Zone code i pick in the drop-down. I want to achieve this using only formulas as i'm distributing the file to a wide audience who are not versed enough with computers to know how to enable macros.

View 3 Replies View Related

Dynamically Set Combobox List Fill Range

Jan 1, 2010

I have a combobox whose list is filled with different ranges, on different sheets, selectable using option buttons. The code is shown below:

Private Sub OptionButton1_Click()
Dim ray
Dim Last
ray = Sheets("Trades").Range("B2:B500")
If OptionButton1 = True Then
With ComboBox1
.List = ray
.ListIndex = 0
End With
End If
End Sub

View 9 Replies View Related

Dynamically Update Chart Source Range

Aug 31, 2007

I'm trying to create a chart and set its source dynamically.

I've tried two methods, the first by setting the XValues of a chart's series collection to a range specified by cells,

ActiveChart.SeriesCollection(1).XValues = range(Cells(9, coloffset + 4), _
Cells(9 + numvsteps, coloffset + 4))

And by using the chart wizard.

Call vchart.Chart.ChartWizard( _
Sheets(resultssheet).range(Cells(9, coloffset + 6), Cells(9 + numvsteps, coloffset + 6)), _
xlLine, , xlColumns, _
Sheets(resultssheet).range(Cells(9, coloffset + 1), Cells(9 + numvsteps, coloffset + 1)), , _
False, "End-End Eff., " & current & "A", "Line Voltage", "Eff. [%]", "test")

Setting the XValues property gives an "Unable to set the XValues property of a series class".

Using the chart wizard, trying to set the category labels parameter of the chart wizard to a range doesn't give an error message, but does leave the category labels section of the chart (when I click and view it) blank.

I've also tried

resultssheet = "Sheet1"
tempstring = "='" & resultssheet & "'!" & "R9C" & (coloffset + 1) & ":R" & (9 + numvsteps) & "C" & (coloffset + 1)
ActiveChart.SeriesCollection(1).XValues = tempstring

When I step through the code, tempstring is "='LineReg Results'!R9C1:R19C1", which is correct, but I still get an "unable to set the xvalues property of a series class" error.

Interestingly, if I record a macro of me setting the category labels, and play it back, I also get the error.

View 7 Replies View Related

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