Free Microsoft Excel 2013 Quick Reference

Refer to Userform object by name??

Is it possible to be able to refer to an object, let's say a label on a userform, just by its name.

i.e.
I have 35 sequentially identified labels which once I know an initial value I want to be able to incrementally update the next label in line.

I know that I can loop through the contrls collection and when I reach the control with the correct name I can update it. I just want to know if there is away to do away with the loop.


Is there a way to refer to Query Tables by name instead of by index
number? Or is there a way to change the index numbers? I have a sheet
with a large number of querys and need to reference them individually,
preferably by name.

Is there a function to return an object reference, given the object name as a string? For example, its spec would be:

I have several labels on a form that I would like to be able to iterate through in a loop. They are named, for example,
Label1, Label2, etc. If I could refer to the objects by name, then I could do something like:

Dim i as long
For i = 1 to ObjCount
   If ObjRef("Label"&i).Value > 0 Then
      ' do something here based on that value
   End If
Next i
Currently my workaround is to have an array of Object, and initialize it by setting each element to one of the labels, then working with the array. Would be nice to address the labels directly.

Is it possible, using the tools available in Excel 2002, to refer to a
worksheet by its name?

I created my own class, and I am trying to add controls on a form to that class. Everything works fine except for the line ctrl = Me.Controls(Rcontrolname) . I know I could do something like "for each ctrl in UserForm1" check if the name matches some model, and then do the rest etc, but I want to specifically reference it by name. What do I need to change?

dim ctrl as control
For i = 1 To 7
        Rcontrolname = "ac_al_" & i
        ctrl = Me.Controls(Rcontrolname)
        Set RACs(i).RACGroup = ctrl)
Next i


Forgive my rustiness with VBA; it's been a couple years since I've done anything substantial. (I did do a search, by the way...)

My spreadsheet has fixed columns, each with a range name, such as "Due_Date". The rows contain individual project data. This project data gets added, edited, deleted, sorted and filtered like crazy.

I edit it with a userform, and if it's a new record or editing an existing one, I know the row I'm dealing with, and make use of the column names and then either Cells or Intersect to put the data where it belongs.

I have code to name new records by naming the row (i.e. turning "16:16", into "Project210". My problem is, for some of the things I wish to accomplish, I can't figure out how to refer to a record by name, if I do not already know it.

While I have other plans, what I am trying to do today is use ActiveCell, and then determining the row I'm in by use of the unique row range name, to return the user back to the same record he/she is working on after doing a sort or filter operation.

Hey all i have aquick question here,

I would like to refer to the current object name that i'm writing the code for in my code.

Example:

I have the code

Private Sub CheckBox102_Click()
'Gives Object number (checkbox102 = 102)
    Dim N As String
    Dim O As String
    Dim Num As String
    O = Me.ActiveControl.Name
    N = Right(O, Len(O) - 5)
    N = Left(N, Len(N) - 2)
    If Len(N) = 3 Then
        Num = Right(N, Len(N) - 2)
    Else
        Num = Right(N, Len(N) - 3)
    End If
msgbox Num
End sub
I would like the line that says Me.Activecontrol.Name to refer to the Object Checkbox102 itself. The reason I need this is because i have the checkbox within a frame and when that "me.activecontrol.name" comes around it refers to Frame1.

Any one have any ideas with this?

Thanks
Ethan

Hi, this is probably a really easy one.

I have this code:

Sub Button17_Click()
Sheets("Blank 1").Activate
Range("E6").Select
End Sub
...to assign to a button to change sheets (obviously...). The trouble is that the sheets in the workbook change name, via another macro. So the sheet known as 'Blank 1', when filled in, might be called something else entirely.

Can I refer to the sheet by some other means to keep this macro working?

Hello,

I will explain this scenario as best I can, please forgive any discrepancies.

I wish to create a dynamic sort of way of referring to defined cells by including an integer in a formula which is taken from another cell.

i.e:

If you have a list of named cells lets say:

value_1
value_2
value_3
etc

and you want to refer to them in a formula by simply typing value_ then have the number part extracted from another cell.

So say in cell:

A1 there is a number 1
A2 there is a number 2
A3 there is a number 3

An so getting back to the formula, it would read something like:

="value_" & A1

Then in other cells

="value_" & A2
="value_" & A3

So as you can see I need it to combine the value from A1 with the text I entered before it to refer to a defined object or defined cell.

Is this possible, can it be done without VB, can anybody be of assistance?

This would be a very useful coding trick to learn as it will save me much time, I regularly have need for such code.

Thanks in advance,

Michael.

Hi,
First post so... thanks a lot!
I need to reference a cell to another cell in a different workbook (not opened), however the name of this workbook contains a variable in its name. Something like:

='C:[Week" & variable &".xls]R23'!$D$3

where variable is the number of the week. I have tried using a cell name instead of a variable:

='C:[Week=($B$3).xls]R23'!$D$3

and I get a dialog box to update values by selecting a file.

I'd appreciate any advice.

Hi,

I am trying to select cells, tables and charts by just knowing their name. Thus if possible I would like to only refer to a chart by its name i.e. not know what worksheet they are on. This seems to work for named ranges of cells/tables, but not charts (I can't get my named charts to appear in the "Define Name" dialog).

I found this code that loops through charts on the activesheet and shows the names. However, I would much prefer not to have to specify the sheet or to loop through all the sheets trying to locate the one with the named chart.


	VB:
	
 GetEmbeddedCharts() 
    Dim myChart As ChartObject 
    Dim myCharts As ChartObjects 
    Set myCharts = ActiveSheet.ChartObjects 
    For Each myChart In myCharts 
        Debug.Print myChart.Chart.Name 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks,

Hi guys. Thanks for any help. I suppose I have not run across this before because I am stuck.

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.

For example:

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.
=OFFSET(AllData,0,0,COUNTA(AllData))

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

These do not work...
ThisWorkbook.Names("AllData_UsedRange").RefersToRange.Address
Evaluate(ThisWorkbook.Names("AllData_UsedRange"))

Thanks...

Hello..thanks for your help in this..

I have a workbook with many tabs (worksheets). The last is a spreadsheet
(let's call it Tab#10) with each row drawing data from different places in
each of the worksheets...so that row 1 will draw data from tab#1, row two
from tab #2...etc.

I would like to manually link the cells in row one (in Tab#10) to tab#1, and
then drag down to fill the rest of the rows (in Tab#10). I was then going to
use the "search & replace" feature to replace the reference to tab#1 by the
correct tab name for row 2 onwards.

QUESTION: There must be a smarter/quicker/more efficient way of doing this,
and I know one of you Excel whizzes knows it!!

p.s. I am not a macro expert..but I just copied a macro code from this site
for automatically naming tabs and it WORKED - I am SO happy.. so I'm
encouraged.

Example: I have a workbook with 3 sheets: Report, DeptA, and DeptB. When I
change the value in the cell named Dept in Report to "A", I want the other
cells in Report to retrieve values from DeptA spreadsheet, and when I enter
Dept="B" I want the values from the DeptB spreadsheet.

In other words, I want the reference to the other worksheets to change based
on the value in one cell. Can I do this by combining formulas or do I have to
use VBA?

I tried something like [="Dept"&(dept)&!A1] (if A1 was the cell I wanted),
but got problems combining text and formula. I know I could use IF, but not
if there are 20 different sheets. Besides, shouldn't there be a more elegant
way to do this?

xl-2007
In a worksheet formula, is there a way to refer to another worksheet by
index number an not by it's given name?
I'd rather not us VBA if possible.
Thanks.
DK

Hi All

I have a dynamic range name that includes all my chart data including
rows and columns as the number of both change a daily basis. So one
day I will have three row series and four columns and another six
series and two columns of data.

I can tell my chart to show all the data by setting the data range to
my dynamic name and that works fine but it converts the dynamic name
to row and column references and forgets the dynamic part for the next
day. I then have to manually perform the same set the next day so that
it charts all the series and data again.

I would like to automate this in VB but this is complicated as all my
charts live on a separate sheet and it is painful in VB to work out
which chart should have which dynamic range name.

How can I get my charts to remember the dynamic range name and not
convert to rows and columns?

If this is not possible what is the best practice to loop over all my
charts and reset their data to the appropriate range name?

At the moment my approach is:
* name each chart the same as the dynamic range name
* loop through the charts on my chart sheet, read its name then set
its data to the same dynamic range name

This is quite involved and not very robust or intuitive. What is the
best practice?

Thanks for any ideas?
Chrisso

Hi,

I want the following behaviour:

if the value of a cell changes, then I check if the column the cell is
in is a certain column, if yes then I do some calculation.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
...
End If
End Sub

Question: How can I reference the queried column by name rather than by
number as above?

Thanks!

G'day there One and All,

As you can see from the subject, I'm having a little difficulty with a
Range Object and can't find any reference to the cause of my error when I
check.

I have a Userform with a TextBox, a multiselect ListBox, and 3
CommandButtons - Cancel, Add, Remove. The latter work on the Listbox
entries. Cancel simply unloads the form.

At initialization the contents of a range on worksheet "dSht" are placed
in a string array. The listbox is loaded from that array. The buttons
either manipulate the list, or remove the form from the screen, and the
form's terminate routine places the string array back into the range.

My problem is that all works fine (an odd problem I hear you say!!). The
rest of the story is that it only works fine when I run the code from the
VBE. The range is cleared of its entries; the listbox is filled; the "Add"
& "Remove" buttons do their thing with the changes immediately reflected in
the listbox; and "Cancel" puts the array contents where they belong - in a
named dynamic range.

Running the code from a calling routine:

Public Sub shwGrpFrm()
frmGrpAdmin.Show
End Sub

which is on the front worksheet that I've imaginatively called "Main",
gives me a "1004" run time error. The "Method 'Range' of object
'_Worksheet' failed".

It's pretty obvious that I'm misunderstanding some subtlety of the Range
Object, but I can't figure out where to start looking. John WALKENBACH's
"Excel 2003 Power Programming with VBA" didn't show me anything obvious,
but that's likely to be a function of my thick head. I intend to read what
I can find in it again tonight.

I've tried referring to the worksheet by name -
Worksheets("Data").Range(Cells...

but that didn't work either.

Here's what I've got so far. Parts are commented for later reference by
those with no idea of Excel, not just for me. On completion I intend to
have comments as far as the eye can see, since there's a real good chance
that it won't be me maintaining it.

Thanks for looking at it.
Ken McLennan
Qld, Australia

Private Sub CommandButton2_Click()
' "Remove"
gNum1 = 0
For gNum = 0 To ListBox1.ListCount - 1
' Debug.Print gNum; " "; gStrArray(gNum + 1)
If ListBox1.Selected(gNum) Then
gStrArray(gNum + 1) = ""
gNum1 = gNum1 + 1
End If
Next
ListBox1.List = rngSrt(gStrArray, False)
ReDim Preserve gStrArray(UBound(gStrArray) - gNum1)
ListBox1.List = rngSrt(gStrArray, True)
End Sub

Private Sub CommandButton3_Click()
' "Add"
ReDim Preserve gStrArray(UBound(gStrArray) + 1)
gStrArray(UBound(gStrArray)) = Me.TextBox1.Text
ListBox1.List = rngSrt(gStrArray, True)
TextBox1.Text = ""
TextBox1.SetFocus
End Sub

Private Sub UserForm_Initialize()
' Set range "Groups" as object
Set gRng = Range("Groups")
' Get column number of range "Groups"
rngCol = gRng.Column
' Get number of cells in range "Groups"
rngCellCnt = gRng.Cells.Count
' Get address of 1st cell in range "Groups"
rng1stCell = gRng.Cells(1).Row
' Get values of each cell and save in general use string array
ReDim gStrArray(rngCellCnt)
For gNum = 1 To UBound(gStrArray)
gStrArray(gNum) = gRng.Cells(gNum).Value
Next
' Set userform listbox from array
ListBox1.List = gStrArray
gRng.ClearContents
End Sub

Private Sub UserForm_Terminate()

' Initialize variable to hold range object for this routine only
Dim rngTgt As Range
' Set range address to the size of "gStrArray"
' Start by setting number of rows/cells to the number of array elements
rngCellCnt = UBound(gStrArray)
' Then set the range to this size. "Groups" has only a single column
' the number of which is known from the form initialization
Set rngTgt = dSht.Range(Cells(rng1stCell, rngCol), Cells(rngCellCnt,
rngCol))
' The "Transpose" function must be used for a column alignment of a
' single dimensioned array
rngTgt.Value = Application.WorksheetFunction.Transpose(gStrArray)
End Sub

Example: I have a workbook with 3 sheets: Report, DeptA, and DeptB. When I
change the value in the cell named Dept in Report to "A", I want the other
cells in Report to retrieve values from DeptA spreadsheet, and when I enter
Dept="B" I want the values from the DeptB spreadsheet.

In other words, I want the reference to the other worksheets to change based
on the value in one cell. Can I do this by combining formulas or do I have to
use VBA?

I tried something like [="Dept"&(dept)&!A1] (if A1 was the cell I wanted),
but got problems combining text and formula. I know I could use IF, but not
if there are 20 different sheets. Besides, shouldn't there be a more elegant
way to do this?

I have the following formula:
=IF(ISBLANK('WP'!D4), "", 'WP'!D4)
where WP is the name of the sheet.
I need to reference the sheet not by name, but by it's number (for example WP could be my second sheet, but tomorrow the name of my second sheet could change to WT).
Thank you for any help.

OK VBA gurus, I have what I believe to be a simple problem here. I am designing a userform that creates dynamic named ranges for Data Validation. This takes the guess work out of it for my end users who are not savvy enough to name a dynamic range on their own.

My problem is that when I get to line 53 of the code and go to assign the "refersto:=" I always wind up with quotation marks in my final refers to formula in the name manager. Consequently my equations are referenced right, but wont work because of the inclusion of extra quotes.

the code as is produces:

="offset(Data!$C$27,0,0,countA(Data!$C$27:$C$1000)-0,1)"

instead of:

=offset(Data!$C$27,0,0,countA(Data!$C$27:$C$1000)-0,1)

This has been frustrating the pants off me for the whole day and everything I have tried doesn't seem to fix the issue. Can you guys tell me how to get this right??

Sub cmdGenoAdd_Click()

Dim NewGeno As String
Dim NewGenoChk As Variant
Dim NewRng  As Variant
Dim CurRow As Long
Dim CurColInt As String
Dim CurCol As String
Dim TopRef As String

    'Promps user for genotype to add
        NewGeno = Application.InputBox( _
                    Prompt:="What is the Name of the genotype you would like to add?", _
                    Title:="Add Genotype", _
                    Left:=(Application.UsableWidth / 2) - (Me.Width / 2), _
                    Top:=(Application.UsableHeight / 2) - (Me.Height / 2), _
                    Type:=2)
                    
        newgenocheck = Left(NewGeno, 1)
        If IsNumeric(newgenocheck) Then
            MsgBox "You cant start your strain with a number...please try again."
        Else
                'Goes into data sheet and ammends the Possible Strains list
                Sheets("DATA").Visible = True
                Sheets("DATA").Select
                If IsEmpty(Range("PosStrains").Offset(1, 0)) Then
                    Range("PosStrains").Offset(1, 0).Select
                    ActiveCell.Value = NewGeno
                Else
                    Range("PosStrains").End(xlDown).Offset(1, 0).Select
                    ActiveCell.Value = NewGeno
                End If
                
                'Starts the Strain's Allele List
                If IsEmpty(Range("PosStrains").Offset(0, 1)) Then
                    Range("PosStrains").Offset(0, 1).Select
                    ActiveCell.Value = NewGeno
                Else
                    Range("PosStrains").End(xlToRight).Offset(0, 1).Select
                    ActiveCell.Value = NewGeno
                End If
                
                'Determines the active row and sets it to CurRow variable
                CurRow = ActiveCell.Row
                'Determines the active col and sets it to CurCol variable
                CurCol = Mid(ActiveCell.Address, InStr(ActiveCell.Address, "$") + 1, InStr(2, ActiveCell.Address,
"$") - 2)
                'Sets TopRef as current col and current row+1
                TopRef = "$" & CurCol & "$" & CurRow + 1
                
                CurColInt = CStr(CurCol)
                NewRng = "offset(Data!" & TopRef & ",0,0,countA(Data!" & TopRef &
":$" & CurColInt & "$1000)-0,1)"
                ActiveWorkbook.Names.Add Name:=NewGeno & "Genotypes", RefersTo:=NewRng
        End If
                     
End Sub


I Solved it! - use Me.OLEObjects("OptionButton" & CStr( i)).Visible = False

I am trying to control the visibility of several buttons using a procedure. I want to specify the buttons by name. The name is dynamically constructed at run time within a for loop. I saw the following code fragment online but cannot get it to work in my Excel macros:

For i = 0 to numItems
  .Objects("OptionButton" & CStr( i)).Visible = False
Next
So the first iteration would have the effect of OptionButton1.Visible = False
The second iterations would have the effect of OpetionButton2.Visible = False
And so on.

Since the code fragment doesn't work, how can I access an object by dynamically constructing its name at run time? Thanks in advance.

Joe

Hello all,

I am currently working on a user form that shall be used to display the contents of the active row.

Thus I created a form that contains e.g. 20 Labels and 20 TextBoxes. The names of the fields are currently 'Label1', 'Label2' ... 'Label20' and 'TextBox1', 'TextBox2' ... 'TextBox20'.

The values of the Labels and Textboxes shall be set with the 'Initialize' function.

As I have 20 Labels I am currently trying to figure out the best way to set the values when the form is loaded.

Is there any chance to refer to the objects by using them with FOR ... NEXT for example like this:

FOR x = 1 to 20
MyForm.Label(x).Caption = ActiveSheet.Cells(1, x).ValueMyForm.Label(x).Text =ActiveSheet.Cells(ActiveCell.Row, x).ValueNEXT

This would be much easier than defining the values separately for each Label and TextBox.

I already tried to define a (string) variable that combines the correct object name (e.g. vLabel = "MyForm.Label" + Str(x) + ".Caption") but this does not work as this is not an object.

Could you please give me a hint ?

Many thanks in advance for your help !

Claus.

P.S.: I am pretty new to VBA, so please excuse if this is a dumb question.

Hello..thanks for your help in this..

I have a workbook with many tabs (worksheets). The last is a spreadsheet
(let's call it Tab#10) with each row drawing data from different places in
each of the worksheets...so that row 1 will draw data from tab#1, row two
from tab #2...etc.

I would like to manually link the cells in row one (in Tab#10) to tab#1, and
then drag down to fill the rest of the rows (in Tab#10). I was then going to
use the "search & replace" feature to replace the reference to tab#1 by the
correct tab name for row 2 onwards.

QUESTION: There must be a smarter/quicker/more efficient way of doing this,
and I know one of you Excel whizzes knows it!!

p.s. I am not a macro expert..but I just copied a macro code from this site
for automatically naming tabs and it WORKED - I am SO happy.. so I'm
encouraged.

Hi,

Is it possible to reference a range object by referring to cells in R1C1 style format?
For example I have tried to write Range(R5C2:R10C2) instead of Range("b5:B10") but it will not work.
If one can't do this then I am surprised because most text books say that excel does most things
behind the scenes in R1C1 notation.

Thanks

PS this is my first posting so hope I am following correct posting procedures.