Free Microsoft Excel 2013 Quick Reference

Create named range in vba Results

I've looked through the OzGrid VBMacros Help File, which I think is excellent, but can not find the solution to my particular problem. I may be trying to solve the incorrect problem so I'll state my direct question and then why I'm trying to do it.

Question 1: The example code below is supposed to let you define a named range from within VBA. When I run the example code and then look in the WorkSheet, the named range is not defined. If it is possible to do this, please provide a simple function that will illustrate it.


	VB:
	
Range("C4:R9").Name = "SomeName" 
 'or
Set tempRange = Range("C4:R9") 
tempRange.Name = "SomeName" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Question 1A. Can the pure numeric way of referencing cell ranges be used in the above functions? If so, what is the proper syntax e.g "C4:R9" --> something like (3,4:18,9) which doesn't compile

Question 2: (the reason for Question 1) Is there a way to make named ranges defined by a function available to other functions? This is what I am attempting to do in Question 1. It seems logical to me that since I can read and reference named ranges created in the worksheet and that I can "create" them in VBA, that it should be able for the named range to "travel back" to the worksheet. I plan on defining the named ranges based on results generated from earlier steps so that I can reference them when I need them in other functions. I've had no problem referencing the named ranges define while in the Worksheet but now I want to figure out my range based on the data that is generated at run time and then let other code reference those names.

Perhaps there is some more appropriate way to make the names "globally" available and persistant at least until the file is exited.

Thanks

I've got a formula where I would like to refer to a value from the previous sheet, but this value is at the end of a column and is therefore dynamic in it's location.

For example, if the current sheet is called "May", I would like to refer to the range called "AprilIncome" on the previous sheet, which is at the end of column D.

Similarly, if the current sheet is called "June", I would like to refer to the range called "MayIncome" on the previous sheet, which is at the end of column D.

And so on, using all 12 months of the year. In other words, I've got a sheet for each month of the year, and each month should refer to the previous month's income total.

Can anyone help with code for naming the previous month's income total, depending on the current sheet (month) selected?

Many thanks
Pete

I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied.

The Syntax I am having a problem with is

	VB:
	
) 
    Dim DVListName As String 
     
    DVListName = "DV" & sListName 
    Application.Goto Reference:=sListName 
    With Selection.Validation 
        .Delete 
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
        xlBetween, Formula1:="=" & DVListName 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It is the Formula1:="=" & DVListName that is creating the headache.

The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.

Am I missing something simple?

Cheers

Hi,

I have recorded the following macro to select a range of data from a worsheet and create a pie chart.

I am looking for a macro which will do this for multiple worksheets where the data is exactly in the same range in each worksheet. The macro is to continue creating the chart for each worksheet until it can find no further worksheets.

Sub proj1()
' proj1 Macro
' Macro recorded 26/08/2008 by LUKKAN1
Range("A1:x2").Select
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=Sheets("Project 1").Range("A1:x2"), PlotBy _
:=xlRows
ActiveChart.Location Where:=xlLocationAsObject, Name:="Project 1"
ActiveChart.ChartTitle.Select
Selection.Font.Bold = True
Selection.AutoScaleFont = True
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _
True, ShowValue:=False, ShowPercentage:=True, ShowBubbleSize:=False
End With
End Sub

Does anyone know if this can be done? I am new to VBAs.

Regards
N

I have previously created a patch (with help from this forum) that allows you to choose a spreadsheet to open and then change details in the spreadsheet, save the changes and then close the spreadsheet.

I am trying to use a similar patch and I can't get it to work. I can get the spreadsheet to open but I can't get the change to take place. What I want it to do is open the spreadsheet and then go to the tab called Summary Form. When it is in that tab I need it to unprotect it. It then has to look at each cell between Column G19 and G40 and if there is a 0 in that cell then do nothing. If there is a number in there then I want it to unhide a tab. I am using a similar code from the original spreadsheet where it works fine but it doesn't seem to like being transported into this patch. Can anyone see a way for me to make this work?

Modified Code and original code:

Original Code:

Private Sub Worksheet_Change(ByVal Target As Range)
'Set sheet tab to hide
Dim i As Integer
Dim Sheetname As String
Dim Sheetname2 As String
Dim SheetLen As Integer
Dim SheetLen2 As Integer
If Intersect(Target, Range("$G$19:$G$39")) Is Nothing Then Exit Sub
Sheetname = Target.Offset(0, 9).Value
SheetLen = Len(Sheetname)
Sheetname2 = Target.Offset(0, 10).Value
SheetLen2 = Len(Sheetname2)
For i = 3 To Worksheets.Count
If Left(Sheets(i).Name, SheetLen) = Sheetname Then
Sheets(i).Visible = False
ElseIf Left(Sheets(i).Name, SheetLen2) = Sheetname2 Then
Sheets(i).Visible = True
End If
Next i
End Sub

Modified Code:

Private Sub Workbook_Open()
' Get the appropriate file information
Dim sFileName As String
sFileName = Application.GetOpenFilename
' They have cancelled.
If sFileName = "False" Then Exit Sub
Workbooks.Open Filename:=sFileName
' Unprotects the workbook
ActiveWorkbook.Sheets("Summary Form").Unprotect ("test1")
'Set sheet tab to hide
Dim i As Integer
Dim a As Integer
Dim Sheetname As String
Dim Sheetname2 As String
Dim SheetLen As Integer
Dim SheetLen2 As Integer
For a = 19 To 40
If Intersect(Target, Cells(a, 7)) Is Nothing Then
Exit For
End If
Next a
Sheetname = Target.Offset(0, 9).Value
SheetLen = Len(Sheetname)
Sheetname2 = Target.Offset(0, 10).Value
SheetLen2 = Len(Sheetname2)
For i = 3 To Worksheets.Count
If Left(Sheets(i).Name, SheetLen) = Sheetname Then
Sheets(i).Visible = False
ElseIf Left(Sheets(i).Name, SheetLen2) = Sheetname2 Then
Sheets(i).Visible = True
End If
Next i
ActiveWorkbook.Sheets("Summary Form").Protect ("test1")
' Save & Close the updated workbook
ActiveWorkbook.Close True
' Close the update workbook without saving
ActiveWorkbook.Close False
End Sub

Any help would be greatly appreciated.

Thanks

Hi all,

I want to create several dynamic named ranges on a worksheet so that in VBA or elsewhere I can just reference the name, and return the entire range of cells with data in them.

The basic formula is something like this:
=OFFSET('METD ALL'!E:E,0,0,MATCH("*",'METD ALL'!$E:$E,-1)-1,1)
(all credit to ozgrid.com !)

Unfortunately, during my macro, I need to clear all rows below the title row, and this is when the named range formula becomes:

=OFFSET('METD ALL'!#REF!,0,0,MATCH("*",'METD ALL'!$E:$E,-1)-1,1)

So, I want to modify it so that if there is only 1 row in the column (the title), then it returns 0, but if there are more, then I want it to return all the rows from 2 onwards (column E will never have any empty cells as it's a key column)

I tried to do this:
=IF(MATCH("*",'METD ALL'!$E:$E,-1)>=2,OFFSET('METD ALL'!E:E,0,0,MATCH("*",'METD ALL'!$E:$E,-1)-1,1),'METD ALL'!$E:E)

but it is missing something, and returns an incorrect range.

Please can you help?

thanks

Philip

Thanks for any suggestions.

Here is my problem. I have little experience with charts and am modifying a project created by someone else.

Data streams into my workbook by way of DDE. Initially, at the start of every day, there is no data in the range being referred to by my workbook names.

These dynamic workbook names are used as the series' source in my chart. Because the references are not yet valid, for lack of data, a constant warning message is displayed when my DDE links are updated. The popup is modal, stops my code, and is obviously an unacceptable problem.

Application.DisplayAlerts does not work and I would rather not use it to mask a problem even if it did work.

Here is an accurate simplification of my design.

AllData is a static named range that refers to the range A2:A65536

AllData_UsedRange is a dynamic named range that refers to the subset range of AllData that contains data. All of the data is numeric, is entered consecutively, and begins at the first cell on down. A2:A?.
=OFFSET(AllData,0,0,COUNTA(AllData))

To "solve" my problem, I modified the formula that defines AllData_UsedRangeto this:

=If(ISERROR(OFFSET(AllData,0,0,COUNTA(AllData)),0,OFFSET(AllData,0,0,COUNTA(AllData)))

If the formula is an error, return a zero. If not, return the reference.

The popup went away and joy set in until I could not get the range address of the name AllData_UsedRange in my VBA code. Even after data was streaming in.

In summary:
AllData RefersTo A2:A65536
AllData_UsedRange RefersTo the range containing data within AllData.
AllData_UsedRange is used as a source for a chart series.

How can I avoid the popup errors until the reference is valid?

Thanks!

I wrote a macro which breaks out a series of accounts based on a user defined range and pulls in the GL data for those accounts by company (each company is in it's own column). I'd like to SUM the account values at the bottom of the worksheet for each company within the macro. The issue is the end row will change based on the user defined range.

For the life of me I can't figure out how to do it...

The first row is always the same (Row 15). See an example below:

I need to figure out how to create a dynamic SUM in VBA. Any help would be greatly appreciated.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP File Edit View Insert Options Tools Data Window Help AboutB18E19H20=
ABCDEFGH12 Example 1  Example 2  Example 313 2008  2008  200814DescriptionYTD Jun DescriptionYTD Jun DescriptionYTD Jun15Example 1           100.00   Example 2            100.00  Example 3           100.00 16Example 1           200.00   Example 2            200.00  Example 3           200.00 17    Example 2            300.00  Example 3           300.00 18Total           300.00     Example 3           400.00 19    Total            600.00    20       Total            900.00 Sheet1 
[HtmlMaker light Ver1.11] 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.

Hi all,

I'm creating a macro to copy a named range on one worksheet to another range in the same workbook. The cell reference in the second work book is determined by a variable row (as it's dependent on a date lookup). I've created a named range on the original worksheet called UpRow01 which shows the row number for the area on the second sheet. I've used this macro code before in other workbooks, but it doesn't seem to work in this case. I get a Compile Error "Can't find project or library" when I attempt to run the macro. The error occurs on line 2 at "UpRow."

I haven't used this code in a while, so perhaps I've forgotten something? Any help would be appreciated.

Sub ComMnthFcst_01()

Sheets("Current").Unprotect (Authorized)
UpRow = Range("UpRow01").Value
Range("CF_01").Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Current").Range("D" & UpRow).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Sheets("Current").Protect (Authorized)
Sheets("Jan").Select
Range("a1").Select
Range("C11").Select
End Sub

I am currentl trying to send an email based on a value i have offsetted to. What i have is a Word macro, that opens an Excel File, finds a variable WordFn and offsets to two spaces to the left and declare a variable LocationFN.

What i am doing then is saying If LocationFN="Finance"
then send an email to finance_group@company.com
in the Message have WordFN has the following form attached ExcelFN
else If LocationFN="Engineering"
then send an email to engineering_group@company.com

Code:
Set aDoc = ActiveDocument
     'Word document name open (aDoc) is declared as WordFn
    WordFN = Left(aDoc.Name, Len(aDoc.Name) - 4)
     'Open LineClearance.xls from the following path
    WorkbookToWorkOn = "C:formsindex.xls"
     'If Excel is running, get a handle on it; otherwise start a new instance of Excel
    On Error Resume Next
    Set oXL = GetObject(, "Excel.Application")
    If Err.Number  0 Then
        ExcelWasNotRunning = True
        Set oXL = New Excel.Application
        oXL.ScreenUpdating = False
    End If
    On Error GoTo Err_Handler
     'Open the workbook WorkbookToWorkOn LineClearance.xls
    Set oWB = oXL.Workbooks.Open(FileName:=WorkbookToWorkOn)
     'Initialise Sheet1
    Set oSheet = oWB.Worksheets(1)
     'Find last cell with data in column A and set it for the range
    RowI = oSheet.Cells(oSheet.Rows.Count, "C").End(xlUp).Row
    With oSheet.Range("c1:c" & RowI)
        'Find the variable WordFN within the Range in column A
        Set c = .Find(WordFN, LookIn:=xlValues)
        If Not c Is Nothing Then
            FirstAddress = c.Address
            Do
            Do
            ProductFN = c.Offset(0, -1).Value
            LocationFN = c.Offset(0, -2).Value
            'create a loop for Offsetting to the next
            aCol = aCol + 1
            'Declare ExcelFN as the Offsetted Value
            ExcelFN = c.Offset(0, aCol).Value
            'If there are more than one value Offset then create a List
            If ExcelFN  "" Then
            ExFnList = ExFnList & ExcelFN & "|"
            'Declare new doc as bDoc and Open the following path(s) C:forms" & ExcelFN & ".Doc"
            Set bDoc = Documents.Open("C:forms" & ExcelFN & ".Doc")
            'Set Title of aDoc = bDoc
            bDoc.BuiltInDocumentProperties("Title") = _
            aDoc.BuiltInDocumentProperties("Title")
            'Update the Title Fields by calling the UpdateStoryRanges procedure, updating the Title as Batch/Lot no
            Call UpdateStoryRanges(bDoc)
            'Print off the Document(s) opened
            bDoc.PrintOut
            'Close Document(s) without saving changes
            bDoc.Close savechanges:=wdDoNotSaveChanges
            If LocationFN = "Finance" Then
            Call finance_group
            ElseIf LocationFN = "Engineering" Then
            Call engineering_group
            Else
            MsgBox ("Cannot find name")
            End If
            End If
            'Loop Until 8 times, Column A = WordFN, Column B, C, D, E, F, G, H, I = ExcelFN
            Loop Until aCol = 15
            Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address  FirstAddress
            'Display a Userform2 Stating the BHR (Document Name) along with the ExFnList of Forms
            With UserForm2
            .TextBox1 = Chr(10) & "Name:    " & ProductFN & Chr(10) & "Reference Number:   " & WordFN & Chr(10) & Chr(10) &
Chr(10) & "The following forms attached are attached: " & Chr(10) & Chr(10) & Replace(ExFnList, "|", Chr(10)) & Chr(10) &
"Forms for " & WordFN & " have been sent to the printer..."
            .Show
            End With
            Else
            'If a Document Name cannot be found in Excel range then the following message is produced
            MsgBox ("# " + WordFN + " does not contain additional Forms")
        End If
    End With


Hello,
I am trying to create multiple charts on different sheets in a workbook using differing source data for each chart.
I am using the below code to create each chart separately but after three charts have been created I get the error:
Run time Error "1004"
No more new fonts may be applied in this workbook
Can anyone tell me why I can not create more than three charts?
Is there another way that I can create multiple charts that will be displayed on multiple pages using differing source data?
Thank you very much in advance for any assistance.
Melinda

Private Sub MaritalPage()
MsgBox "MaritalPage"
Dim ch As ChartObject
Set ch = Worksheets("Marital").ChartObjects.Add(100, 150, 600, 350)

'r = row in lookup - the code finds the subheadings in lookup and the row number is represented by r
'i = counter for finding values like not stated or other necessary values

i = 1
R = 1
Sheets("Lookup").Activate

Do 'Iterate through first column of lookup sheet to find certain ranges
Select Case Cells(R, 1)

'Married Marital Status of Main Driver CHart
Case Is = "H3.Marital Status of Main Driver"
i = 1
Do
If Cells(R + i, 2) = "Not Stated" Then
Exit Do
End If
i = i + 1
Loop

StrRange = "I" & R + 3 & ":M" & (i + R - 1)
ch.Chart.ChartWizard Source:=Worksheets("Lookup").Range(StrRange), gallery:=xlLine, PlotBy:=xlRows, Title:="Marital Status"
ch.Chart.SeriesCollection(1).XValues = "=Lookup!R2C9:R2C13"
ch.Chart.ChartType = xlColumnStacked100
For x = 1 To 4
ch.Chart.SeriesCollection(x).Name = "=Lookup!R" & R + x + 2 & "C2"
Next x

With ch.Chart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnit = 20
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
'Set the Plot area to white
ch.Chart.PlotArea.Border.ColorIndex = 16
ch.Chart.PlotArea.Border.Weight = xlThin
ch.Chart.PlotArea.Border.LineStyle = xlContinuous
ch.Chart.PlotArea.Interior.ColorIndex = 2
ch.Chart.PlotArea.Interior.PatternColorIndex = 1
ch.Chart.PlotArea.Interior.Pattern = xlSolid

'Ensure all X Labels are horizontal
ch.Chart.Axes(xlCategory).TickLabels.Alignment = xlCenter
ch.Chart.Axes(xlCategory).TickLabels.Offset = 100
ch.Chart.Axes(xlCategory).TickLabels.ReadingOrder = xlContext
ch.Chart.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
ch.Chart.Legend.Position = xlBottom

'Change Colour
ch.Chart.SeriesCollection(1).Interior.ColorIndex = 21
ch.Chart.SeriesCollection(2).Interior.ColorIndex = 42
ch.Chart.SeriesCollection(3).Interior.ColorIndex = 27
ch.Chart.SeriesCollection(4).Interior.ColorIndex = 43

Sheets("lookup").Select
R = R + 1

Case Else
R = R + 1
i = 1
End Select
Loop Until R = 2500
End Sub

I've spent several hours combing this site and others looking for my solution. I've found bits and pieces, but what I try never seems to get what I need--hope someone can help with this.

Using Excel 2003, I have a named range. When the user clicks on a button, a listbox should appear (multiselect) that the user chooses 3-4 items. Something should make the list go away-- a button? then followed by the those items he selected appearing in a cell close by.

I wanted to use Data Validation List, but it doesn't support multiselect, so VBA code it is! Any help?

Thanks
Mari

I have found the following code that generally meets my requirements except that it asks for user input. The input requirements are not necessary (i.e, File name, Group Name, Cell Range for Field Names, and Top Left Coordinate for Data Range are all fixed. The one exception to the fixed range: I would like the macro to determine the bottom right coordinate in the data range condition and set the macro value to that.

Can anyone point me in the right direction to hard code the variables?

Code:
Sub MakeXML()
' create an XML file from an Excel table
Dim MyRow As Double, MyCol As Double, Temp As String, YesNo As Variant, DefFolder As String
Dim XMLFileName As String, XMLRecSetName As String, MyLF As String, RTC1 As Double
Dim RangeOne As String, RangeTwo As String, Tt As String, FldName(99) As String

MyLF = Chr(10) & Chr(13)    ' a line feed command
DefFolder = "C:"   'change this to the location of saved XML files

YesNo = MsgBox("This procedure requires the following data:" & MyLF _
 & "1 A filename for the XML file" & MyLF _
 & "2 A groupname for an XML record" & MyLF _
 & "3 A cellrange containing fieldnames (col titles)" & MyLF _
 & "4 A cellrange containing the data table" & MyLF _
 & "Are you ready to proceed?", vbQuestion + vbYesNo, "MakeXML CiM")
 
If YesNo = vbNo Then
 Debug.Print "User aborted with 'No'"
 Exit Sub
End If

XMLFileName = FillSpaces(InputBox("1. Enter the name of the XML file:", "MakeXML CiM", "xl_xml_data"))
If Right(XMLFileName, 4)  ".xml" Then
 XMLFileName = XMLFileName & ".xml"
End If

XMLRecSetName = FillSpaces(InputBox("2. Enter an identifying name of a record:", "MakeXML CiM", "record"))

RangeOne = InputBox("3. Enter the range of cells containing the field names (or column titles):", "MakeXML CiM", "A1:E1")
If MyRng(RangeOne, 1)  MyRng(RangeOne, 2) Then
  MsgBox "Error: names must be on a single row" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"
  Exit Sub
End If
MyRow = MyRng(RangeOne, 1)
For MyCol = MyRng(RangeOne, 3) To MyRng(RangeOne, 4)
 If Len(Cells(MyRow, MyCol).Value) = 0 Then
  MsgBox "Error: names range contains blank cell" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"
  Exit Sub
 End If
 FldName(MyCol - MyRng(RangeOne, 3)) = FillSpaces(Cells(MyRow, MyCol).Value)
Next MyCol

RangeTwo = InputBox("4. Enter the range of cells containing the data table:", "MakeXML CiM", "A2:D8")
If MyRng(RangeOne, 4) - MyRng(RangeOne, 3)  MyRng(RangeTwo, 4) - MyRng(RangeTwo, 3) Then
  MsgBox "Error: number of field names  data columns" & MyLF & "Procedure STOPPED", vbOKOnly + vbCritical, "MakeXML CiM"
  Exit Sub
End If
RTC1 = MyRng(RangeTwo, 3)

If InStr(1, XMLFileName, ":") = 0 Then
 XMLFileName = DefFolder & XMLFileName
End If

Open XMLFileName For Output As #1
Print #1, ""
Print #1, ""

For MyRow = MyRng(RangeTwo, 1) To MyRng(RangeTwo, 2)
Print #1, ""
  For MyCol = RTC1 To MyRng(RangeTwo, 4)
  ' the next line uses the FormChk function to format dates and numbers
     Print #1, "" & RemoveAmpersands(FormChk(MyRow, MyCol)) & ""
  ' the next line does not apply any formatting
  '  Print #1, "" & RemoveAmpersands(Cells(MyRow, MyCol).Value) & ""
    Next MyCol
 Print #1, ""

Next MyRow
Print #1, ""
Close #1
MsgBox XMLFileName & " created." & MyLF & "Process finished", vbOKOnly + vbInformation, "MakeXML CiM"
Debug.Print XMLFileName & " saved"
End Sub
As Always 10^6 thanks in advance.

a

Hello, Question one> The code below I copied from the forum. I thought if the merged cells E4:G4 were filled with numbers. Example 44-4444 these numbers would be copied to the sheet name. Well it does if I run the macro manually but errors. After ending the debug. Then it changes to the number. Is there a way to do this automatically without error.

Second part of the question> Also, could a code be able to to create a new workbook with that name. Keeping the original copy before adding a number in the cells??

Thanks in advance Tom

Sub NameSheets()
ActiveSheet.Unprotect
For Each Sheet In Sheets
Sheet.Name = Sheets(Sheet.Name).Range("E4:G4").Value

Next Sheet
ActiveSheet.Protect
End Sub

Hello,

I am returning again after being stumped and surfing for answers for 3 days!

I am hard coding a table in VBA and I cannot get one simple formula to work properly,
My table module is really,kinda, probably, almost a class module.

My function is to count the 0ís in a range of column B. This range will be different each time the macro is run. Other formulas have calculated the upper and lower row numbers for the range in question. And I have these values in two different cells.

All I can think to do is to make a formula to create a range value (in the form of text)
into another cell,. I have done this with no problem.

I have created a value such as B1:B500 or $B$1:$B$500.

Either of these values will return the correct answer if I enter a formula;

=CountIF(B1:B500,0)

The problem is I need to derive the range from another cell, say J1, that contains my range description. Any function I can find tries to interprets my value as text and thus returns 0 when my count is actually 39. In the function dialog my value comes up in quotes so I know it wonít work before I even get the formula entered. I have tried working around this with VB code, also with no success. I cannot seem to get this value declared and set as a Range type.

The promising functions Address, Index, etc. return ďvolatileĒ when they look to me like they should be working. They will not interpret my range name no matter what I do.

Does anybody know how to handle this problem?

Thanks so much, in advance for any help offered, I will be indebted.

how do you create a drop down list for the user to select from in vba? In the code I have, the user has been manually typing in the name of the Key Account Manager:

Code:
I want to remove any margin for error by having them select from a list instead. My named range with the list of names in is
called "Key_Account_Manager"

I have an Excel file with 11 checkboxes on it. Depending upon what the user selects, I want to change the color of the textboxes to either white or yellow. I've created some named ranges in Excel, as well as a VLOOKUP system to support a Loop in the code. The code works precisely how I want it to, but it takes too long to execute (about 10 seconds, even with the screenupdating turned to false during execution.

Any ideas how to speed up the code? It goes through the loop 11 times. I'm already not "selecting" the checkboxes, thinking that that would speed it further. Additional ideas would be great! Thanks in advance

Code:
Sub ColorCheckBoxes2()
    Dim TrueOrFalse As String
    Dim ChkBoxNum As String
    Range("selector").Cut Range("anchor").Offset(0, 1)
Do
        ChkBoxNum = "Check Box " & Range("current_chkbox").Value
        TrueOrFalse = Range("whether_visible").Value
        
        If TrueOrFalse = 0 Then
            ActiveSheet.Shapes(ChkBoxNum).Fill.ForeColor.SchemeColor = 9
            'ActiveSheet.Shapes(ChkBoxNum).Fill.Visible = msoFalse
        Else
            ActiveSheet.Shapes(ChkBoxNum).Fill.ForeColor.SchemeColor = 13
        End If
                
        Range("selector").Cut Range("selector").Offset(1, 0)
Loop Until Range("current_chkbox").Value = 0      'continue until last checkbox

End Sub
Jared

I want to create a named range (I'll call it GoalList) and I've tried to use
something like this:

=OFFSET(PP!Goal,1,0,COUNTA(PP!$C:$C)-1,1)

Unfortunately, there are blank values on column C, so I don't really get to
the last row in the range. I know how to do this with VBA, but I need a
named range. Can someone assist?

Thanks,
Barb Reinhardt

Maybe I didn't name the subject correctly. In general, is there any good documentation on
interfacing to excel from C++? I can get a few things to work but others won't compile. Maybe I have
incompatible dll's? Is this the right newsgroup?

I've been able to cause VC++ 6.0 (no mfc) to create a chart in excel. Eg,

.....

chart->ChartWizard(
(Excel::Range*) range, // source
(long) Excel::xlXYScatter, // Gallery
6L, // Format (1-10)
(long) Excel::xlColumns, // PlotBy
1L, // 1 CategoryLabels
0L, // 0 SeriesLabels
2L,
title,
"Time",
"Value"
);

I can then set the name of the chart with:

chart->Name = "Test Plot";

Now I'd like to turn on the axes major and minor grid lines, but can't figure out the syntax.
I tried this:

chart->Axes(Excel::xlValue, Excel::xlPrimary)->HasMajorGridlines = 1;
but get a compilation error:

main.cpp(870) : error C2664: 'Axes' : cannot convert parameter 1 from 'enum Excel::XlAxisType' to
'const class _variant_t &'
Reason: cannot convert from 'enum Excel::XlAxisType' to 'const class _variant_t'
No constructor could take the source type, or constructor overload resolution was ambiguous
c:_flo5excelmain.cpp(870) : error C2227: left of '->HasMajorGridlines' must point to
class/struct/union

I'd also like to be able to set the y-axis scaling programatically.

Can someone tell me what the correct way to do this is in C++?

thanks,
David

I'm trying to create a dynamic chart using named ranges that will not only
expand or collapse based on the data size, but will also dynamically show
varying number of series. I've seen some posts that appear to address this,
but I can't quite make it happen. My data is structured similar to this:

Date (A1) Series1 (B1) Series2 (C1)
6/15/2007 1000 1500
6/16/2007 1200 1550
6/17/2007 1150 1400

Not only could I have varying number of days starting in cell A2 going down,
but I could also have varying number of series. Today I may have two series
to chart, but next time, I could have 5 series. If I initially create my
chart adding many series--say like 20 (more than I'd ever need), and then
only have data for a few series, the chart will only plot a couple of lines
like I want, but the legend will show placeholders for all 20 series. Is
there any way I can create a dynamic chart that will allow me to only show
the number of series with data (and only show these in the legend)?

Lastly, I want to avoid using VBA since this is going to be a downloadable
report from the web.

Any help would be greatly appreciated.


No luck finding an answer? You could always try Google.