Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Set Combobox Properties In Vba

Hello,

I have read quite a few posts but I still can't seem to figure out how to achieve what I want:

I used VBA to create a new ComboBox (just one). I now want to set the properties of the ComboBox using VBA. I have tried various attempts but I keep getting the message "Object doesn't support this property or method".

My code is:


	VB:
	
, Left:=135.75, Top:=25.5, Width:=142.5, Height:=13.5).Select 
ActiveSheet.Shapes("ComboBox1").ControlFormat.LinkedCell = "C3" 
ActiveSheet.Shapes("ComboBox1").ControlFormat.LinkedCell = ControlFormat.ListFillRange = DestinationRange 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The first part works, where I create the ComboBox.

Note - DestinationRange is equal to A3:A20, which contains the list of the items I want to be in my ComboBox. (This part isn't too important as I am getting an error being it reads it.

Thanks,

Phil


Post your answer or comment

comments powered by Disqus
Hi

I'm trying to set the properties of a ComboBox (from the Control
Toolbox, not the FOrms toolbox) in VBA code. This is because I'm
trying to implement the solution (from contextures.com) to tiny
unreadable data-validation "combos".

Background
- Zooming is not possible - just got too much to fit onto the sheet
- The solution consists of having a combo box shunted out of sight
"elsewhere" on the sheet: when the user clicks in the cell, the combo
box is shifted onto the cell, made visible etc.
- In my adapted solution there are various different combo boxes, each
getting their list data from a different range. The combo box actually
shown depends on the cell the user clicks in.

Trouble is I want to dynamically set various properties of the combo
(e.g. font, Listwidth) in VBA - but in the code example the combo is
referenced as an "OLEObject" (urrghh! I thought we'd got beyond that
"OLEObject" stuff by now!). Naturally this object type doesn't expose
any combobox-specific properties.

Is there a way I can set an object reference to a Combobox object type
in Excel? I've found a ComboBox class in the Object Browser, but this
appears to be the MSForms Combo box. Or is there any reason why I
can't use an MSForms combo?

I'm a very experienced Access programmer, but Excel controls are new to
me.

thanks for any suggestions.

Seb

I'm using Excel 2003

Ok, pivot table VBA studs, help me out he

Imagine you already have one pivot table report based on external data
say a query of an Access DB or perhaps of another Excel file (assume i
was created via the Wizard and Get Data...).

In VBA, the actual query is stored in the CommandText property of th
PivotCache, which is easily viewable by running the code (assumes onl
1 pivotcache for simplicity):

MsgBox ActiveWorkbook.PivotCaches(1).CommandText

You can also set the property in VBA by running the code:

ActiveWorkbook.PivotCaches(1).CommandText = "SELECT * FROM MyTabl
MyTable"

(WHERE clause omitted for simplicity) The query example above would b
the syntax for querying an external Excel database with a range calle
MyTable, and it works great - I can change the query in VBA, an
setting the CommandText property has the effect of refreshing th
cache.

So far so good. Now say I add another pivot table that uses the firs
PivotCache as its source (specifying as such in the Wizard), so tha
refreshing 2 reports only fires 1 query.

I can still run the code to get the sql:
MsgBox ActiveWorkbook.PivotCaches(1).CommandText

But I cannot run the code to set the sql:
ActiveWorkbook.PivotCaches(1).CommandText = "SELECT * FROM MyTabl
MyTable"

It causes a VBA error 1004. If I then remove the 2nd pivot table s
that the cache only has 1 report, it works fine again - I can set th
sql.

This seems clearly to me a bug in Excel. Any clever workarounds?
did see some threads about setting the property requiring a strin
array to get around a string size limitation, but that it is not th
problem, I think that was only in Excel 2000. At any rate in Exce
2003 I can set huge SQL strings, but I can't set anything if the cach
is shared.

Any clever ideas

--
ashortx
-----------------------------------------------------------------------
ashortxl's Profile: http://www.msusenet.com/member.php?userid=104
View this thread: http://www.msusenet.com/t-187041390

Hey guys.

I was wondering if someone could help me. I am writing a vba script that
takes in data, analyzes it, and then copies the results to a new file. I am
having a problem with two things.

1) I am using a template for the new file so there are a lot of formulas
(sums and std) already defined and ready to use. However, there are some
instances where there is a random amount of additional data I have to put in.
So, I have to apply the same formulas to this new data. How do I copy
formulas from one cell to another (allowing for a change in row) in vba?

Lets say cell(1,4) has the formula "=sum(A1:C1)
If I do: cells(4,4).formula = cells(1,4).formula
then cell(4,4) has the formula "=sum(A1:C1)"

How do I make it become "=sum(A4:C4)" using vba?

2) For these new cells, I also have to format some columns (currency, date,
etc) them and also put borders around them. How do I set these properties in
vba?

I greatly appreciate and assistance you could provide.

Thanks,

-Michael

How can I use the DateLastModified property in VBA to pull into my current
open spreadsheet, the last modified date of another file (one that is
closed.) I want, if possible, to add it to a cell.

As always, your help is greatly appreciated.

Thank You!

I'm using Excel 2003

Ok, pivot table VBA studs, help me out here:

Imagine you already have one pivot table report based on external data,
say a query of an Access DB or perhaps of another Excel file (assume it
was created via the Wizard and Get Data...).

In VBA, the actual query is stored in the CommandText property of the
PivotCache, which is easily viewable by running the code (assumes only
1 pivotcache for simplicity):

MsgBox ActiveWorkbook.PivotCaches(1).CommandText

You can also set the property in VBA by running the code:

ActiveWorkbook.PivotCaches(1).CommandText = "SELECT * FROM MyTable
MyTable"

(WHERE clause omitted for simplicity) The query example above would be
the syntax for querying an external Excel database with a range called
MyTable, and it works great - I can change the query in VBA, and
setting the CommandText property has the effect of refreshing the
cache.

So far so good. Now say I add another pivot table that uses the first
PivotCache as its source (specifying as such in the Wizard), so that
refreshing 2 reports only fires 1 query.

I can still run the code to get the sql:
MsgBox ActiveWorkbook.PivotCaches(1).CommandText

But I cannot run the code to set the sql:
ActiveWorkbook.PivotCaches(1).CommandText = "SELECT * FROM MyTable
MyTable"

It causes a VBA error 1004. If I then remove the 2nd pivot table so
that the cache only has 1 report, it works fine again - I can set the
sql.

This seems clearly to me a bug in Excel. Any clever workarounds? I
did see some threads about setting the property requiring a string
array to get around a string size limitation, but that it is not the
problem, I think that was only in Excel 2000. At any rate in Excel
2003 I can set huge SQL strings, but I can't set anything if the cache
is shared.

Any clever ideas?

--
ashortxl
------------------------------------------------------------------------
ashortxl's Profile: http://www.msusenet.com/member.php?userid=1043
View this thread: http://www.msusenet.com/t-1870413905

Hey guys.

I was wondering if someone could help me. I am writing a vba script that
takes in data, analyzes it, and then copies the results to a new file. I am
having a problem with two things.

1) I am using a template for the new file so there are a lot of formulas
(sums and std) already defined and ready to use. However, there are some
instances where there is a random amount of additional data I have to put in.
So, I have to apply the same formulas to this new data. How do I copy
formulas from one cell to another (allowing for a change in row) in vba?

Lets say cell(1,4) has the formula "=sum(A1:C1)
If I do: cells(4,4).formula = cells(1,4).formula
then cell(4,4) has the formula "=sum(A1:C1)"

How do I make it become "=sum(A4:C4)" using vba?

2) For these new cells, I also have to format some columns (currency, date,
etc) them and also put borders around them. How do I set these properties in
vba?

I greatly appreciate and assistance you could provide.

Thanks,

-Michael

Puzzle:
The code example below causes an "Object doesn't support this property or method (Error 438)" message. The Compiler sees the word "ComboBox1" in the code before it gives itself a chance to create the Object and make references to it as a Member of the Sheet it was created in.

Question:
How can Code be used to add a ComboBox to a worksheet and set properties such as Fonts and FillColor for that ComboBox without using or adding a Form? (See: Experiment Goals and the Code example below for a better explanation.)

Experiment goals:
Without using the Forms Toolbar and without adding a Form to the Project.
In VBA code,
1. Add a ComboBox to a Worksheet.
2. Format the ComboBox (e.g. Change its Font, FillColor, Placement etc. See: code example below)
3. Do the above in "One" Run. (In other words, without asking the user to run the program twice.)

Note: The desired results can be viewed by adding Conditional Compiler options to create the object on the first Run, and Format it on the Next. For an explanation of why this workaround can't be used See: Goal Notes (below code example).


	VB:
	
 ComboBoxTest() 
    Dim strtHrBox As Shape 
     
     'Create an Hour ComboBox.
    Set strtHrBox = Application.ActiveSheet.Shapes. _ 
    AddOLEObject(ClassType:="Forms.ComboBox.1", _ 
    Left:=249.75, Top:=38, Width:=30, Height:=15.5) 
     
     'Error: Object doesn't support this property or method (Error 438)
     'Note: ComboBox1 doesn't exist until after Run-time
     'The Compiler is complaining about the non-existance of a
     'Sheet Member it hasen't given itself a chance to create.
    With Application.ActiveSheet.ComboBox1 
        .AutoSize = False 
        .BackColor = RGB(197, 197, 197) 
        .ColumnCount = 1 
        .ColumnWidths = 1 
        .Font.Name = "Small Fonts" 
        .Font.Size = 7 
        .Font.Bold = True 
        .LinkedCell = "Sheet1!E2" 'Change this to match your Sheets Name.
        .ListFillRange = "Sheet1!B2:B13" 'Change this to match your Sheets Name.
        .ListRows = 12 
        .ListWidth = 28 
        .Placement = xlFreeFloating 
        .SelectionMargin = False 
        .TextAlign = fmTextAlignLeft 
        .Name = "StartHrBox" '
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Goal Notes:
Getting this to work in one pass or one run will allow the code to hook to an event trigger. Or at the very least I won't have to ask the user to run the program twice in order to display an object correctly on screen. Figuring out how to do this without using Forms allows me to embed the Controls in Shapes.

Class Note:
The ComboBox Class has all the properties and members that need to be used for this experiment but the ComboBox Class seems to be intended for use within a Form. I mentioned this because the With block in the above example works on the second Run after ComboBox1 is created as a Member of the Sheet in which it was created by the first run.

LIBRARY NOTE:
Class ComboBox
Member of MSForms
Library MSForms
C:WINDOWSSYSTEMMSForms.TWD
Microsoft Forms 2.0 Object Library

Thank you for your time and help. At the very least I hope this sparks some interesting thoughts, ideas, or questions for the Forum.

Hi, again

Iam getting more and more depressed because nothing wants to work like I want it to work : D.

I'm back on my chart thing, reading Data and displaying it in the chart is working w/o any problems.

So I tried to set the XValues, in the VBA help it is suggested that the XValue property can take a Range or an Array (of Variant). With this thought in mind I assigned my generated Range to the XValue as follows


	VB:
	
 Range 
... 
... 
ActiveChart.SeriesCollection(1).XValues = test 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And the following Error occured "Unable to set the XValues property of the Series class".
Ok, than I tried to create an Array of Variants, I filled it with values and tried the same thing as above. I got the same Error.

But if I try the "Example" solution like


	VB:
	
ActiveChart.SeriesCollection(1).XValues = Worksheets("Sheet1").Range("B3:B16") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it works perfectly.

Maybe someone has an idea.

thx for help

csv

I am trying to set the application.calculation property in an embedded excel workbook object in a powerpoint file using a powerpoint vba macro. With the code below I am receiving the error:

'Unable to set the Calculation property of the Application class'

This would really help performance if I can get this to work.

Public oPres As
Object
Public osld As Object
Public oshp As Object
Public total As Integer
Public count As Long
Public oxlbook As Object
Public oxlapp As Object
Public oxlchtsht As Object
Public oxldatsht As Object
Public oxlqrysht As Object
Public oxlqt As Object
--------------------------------------------------------------
Public Sub update_ppt()

Set oPres = ActivePresentation
total = ActivePresentation.Slides.count
count = 0
sttime = Now
progressForm.Show

    With oPres
        For Each osld In .Slides
            count = count + 1
        
            Call chtupdate(osld)
            
            Call UpdateProgress
        Next osld
    
    End With
progressForm.Hide

endtime = Now
elptime = (sttime - endtime)
tottime = Format(elptime, "nn:ss")
msg = "Update Done" & vbCrLf & vbCrLf
msg = msg & "Elapsed Time: " & tottime
MsgBox msg

End Sub
-----------------------------------------------------------
Private Sub chtupdate(oSlide As Object)
    
For Each oshp In oSlide.Shapes
    If oshp.Type = msoEmbeddedOLEObject Then
        Set oxlbook = oshp.OLEFormat.Object
        Set oxlchtsht = oxlbook.Worksheets("charts")
        Set oxldatsht = oxlbook.Worksheets("data")
        Set oxlqrysht = oxlbook.Worksheets("query")

        oxlbook.Application.Calculation = xlCalculationManual

        For Each oxlqt In oxlqrysht.QueryTables
            oxlqt.Refresh BackgroundQuery:=False
        Next oxlqt

        oxlbook.Application.Calculation = xlCalculationAutomatic

        DoEvents

    End If

    Set oxlqt = Nothing
    Set oxlbook = Nothing
        
    Next oshp

End Sub
Thanks

Erick

I am adding a textbox and trying to set its properties using VBA. I get the
box but cannot figureout haw to set the properties.

Here is part of my code:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=500, Top:=690, Width:=30, Height:=18). _
Select

With Selection
.Placement = xlMoveAndSize
.PrintObject = False
End With

' I want to set the properties of textbox1 here,
' but do not know how to do it. Need to set LinkedCell and Value.

Your assistance is appreciated. Thank you.
--
Don

Hi all,

I am attempting to put a formula array in a range using VBA, but I am getting the exception "cannot set FormulaArray property of range". If I shorten my array function to less than 256 characters, the code works. If I make it longer, then I get an exception. I assumed there was a character limit for array formulas, but I can enter the longer formula by hand (i.e. type in the formula and crtl+sht+entr ) and it works fine. Can anyone tell we why the longer formula won't work using the Range.FormulaArray property in VBA?

Thanks,
Colin

Hello all,

I have a quick question on how to set object properties on UserForm fields with variables?

Here is the general idea of what I am trying to do, obviously it dosnt work:


	VB:
	
 AccountType_Change() 
    Dim DKDoc As Shape 
    Select Case AccountType.Value 
    Case Is = "INDIVIDUAL" 
        DKDoc = "LPOADoc" 
        DKDoc& "Lable.Enabled" = True 
        DKDoc& "Original.Enabled" = True 
        DKDoc& "Signed.Enabled" = True 
        DKDoc& "Completed.Enabled" = True 
    Case Else 
    End Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you for your help!

DrD

How can we set two criterias in autofilter property in vba? For example if there are two columns - departments and grade ... i want that all those rows that have department as Finance and Grade as Grade A should be filtered automatically in VBA Coding. Please reply asap.

I'm trying to hard-code the Width, Height, and Top properties of a Chart's PlotArea at runtime. For some reason, it doesn't want to obey the Top and Height settings, but it always follows the Width setting (maybe because that's the maximum width of the Chart itself).

I've tried putting the code for setting those properties both BEFORE the Chart dynamically fills in data and AFTER. In either case, the final results are not what was set.


	VB:
	
 CurrentChart = Worksheets("Charts").ChartObjects(2).Chart 
 
CurrentChart.SetSourceData Range("Chart2Area") 
 
With CurrentChart.PlotArea 
    .Top = 22 
    .Width = 581 
    .Height = 285 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any ideas? Thanks in advance! Auto Merged Post;

I haven't resolved it, but I found a workaround sort of. I set the Height setting really low--to 10 for example--then I set the Top and Height settings.


	VB:
	
 CurrentChart = Worksheets("Charts").ChartObjects(2).Chart 
 
CurrentChart.SetSourceData Range("Chart2Area") 
 
With CurrentChart.PlotArea 
    .Height = 10 
    .Top = 22 
    .Width = 581 
    .Height = 285 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Seems to help....

I am using a PrintOut statement in a macro.
I would lile to go one step further to control the printer properties.
I could not see how to do that since this is not recorded by the macro recorder.

Would some of you know how to set a printer property in VBA?

Thanks

Hi;
Is it possible to make Excel take the TopMargin value from
a variable or a cell. I tried a number of variations and
VBA keeps sending message: 'object does not support this
property or method'
e.g.
....
upmargin = Range("l2")
prtsheet.TopMargin = Application.InchesToPoints(upmargin)
....

Thanks.

I want to toggle between two ranges (i.e. reset the same named range to
either option, depending on user input).

The named ranges are used by graphs, and show either (option 1) a baseline
data point and 12 months of current data, or
(option 2) 24 months of data.

I tried doing this change on the worksheet and ran into big problems, and
decided I should also look at setting these in VBA and
see if it would be easier. The problem is that when I go back into the
named range, it shows the correct "formula" but
it is returned as a string instead of a reference, e.g.
="OH2!$C$49,OH2!$C$20:$C$31"
instead of
=OH2!$C$49,OH2!$C$20:$C$31

Any/all help greatly appreciated,
Keith R
XL97

Here's what I've got so far:
----------------------------------------------------------------------------
-----------------------------
"Active" is a named range =VLOOKUP(Info!$B$2,Info!$B$4:$C$12,2), and works
properly-
it returns a sheet name, for example, OH2, GMC2, etc.
----------------------------------------------------------------------------
-----------------------------
Public Function ChangeGraph(GType As Range, ActiveSite As Range)
'function can't be put in put in the same cell as Gtype, which is fine

If GType.Value = 1 Then
ActiveWorkbook.Names.Add Name:="Graph01_A", _
RefersTo:=Evaluate(Names("active").Value) & "!$C$49," & _
Evaluate(Names("active").Value) & "!$C$20:$C$31"
ActiveWorkbook.Names.Add Name:="GraphsXAxis", _
RefersTo:=Evaluate(Names("active").Value) & "!$A$7," & _
Evaluate(Names("active").Value) & "!$A$20:$A$31"

ElseIf GType.Value = 2 Then
ActiveWorkbook.Names.Add Name:="Graph01_A", _
RefersTo:=Evaluate(Names("active").Value) & "!$C$8:$C$31"
ActiveWorkbook.Names.Add Name:="GraphsXAxis", _
RefersTo:=Evaluate(Names("active").Value) & "!$A$8:$A$31"

Else
MsgBox "Only values of 1 or 2 can be accepted in ChangeGraph
Function", , "Error: Value Out Of Range"
End If

'I don't set Gtype back to a value so it returns zero- which is
'fine because I just use it to trigger the named range change
'while avoiding the volatile issue

End Function

In VBA within Excel, the worksheets have a name property and a (name)
property. One the user can change by right-clicking on the worksheet
tab and change the name. This is also the same name field with which i
know how to run a loop. My question is how to do i use the OTHER name
field (the only that can only be changed within the VBA properties
field) to run a loop?

The purpose is to run a macro loop regardless of the names of the
worksheet (i can't just lock off the worksheet names, they need to be
left open to change)

Any help is greatly appreciated.

Under the File-->Properties-->Custom tab, if i add in a custom field called "Revision", how do i call this value in VBA?

Thanks
Schwizer

Hello!

I have the following - strange - problem.

I want to set the interior color of a range object in VBA. So far so
good. I created a code and added it to the click event of a button. And
it works fine:

Worksheets("Sheet3").Range("R1").Interior.ColorIndex = 17

But I want to have this code somewhere else. I wrote a class which is
called via a function, which is embedded in a cell. The function of the
cell is =fctXY(a;b;c) etc...

This function uses my object which calls the function that changes the
cell color. But nothing happens. The color doesn't change? I already
tried to turn the automatic cell calculation off, but the same result -
nothing happens. I also can not write a value into another cell when
I'm in the class function which is called by the formula in the cells

Can someone tell me why? Is there a lock for all the cells while the
formula is calculated? Can I turn this off?

Thank you for your help!

Bye,
Christoph

How do I refer to a named range in VBA.

I have a Range named "Parts" I use for a ComboBox list.

How can I refer to the selected item by the range name or is it easier to refer to the ComboBox selection in VBA.

I want to past the item selected in the ComboBox into another sheet for a parts list.

Thanks!!

Hi;

I working on a loop that would find blank cell. These cells have a formula in excel, but if certain conditions are met, the cell is left blank. What property in VBA checks for blank cell? I don't thin it's the "empty" property??

Hi all

I have constructed a questionnaire in the form of a user form (form1) which leads to another user form (form2), which maps the user to 4 types.

The form2 has a command button. Based on a type, the user gets some information in Form 3. The information in form3 is unique to each type.

My question arises in a scenario, when the user is mapped to more than one scenario.

I had planned that all four types would be displayed in the form of colored labels. If the user is mapped to more than one type, then the corresponding colored labels would be highlighted.
I want to ensure that the user can toggle between only the highlighted label boxes.
Secondly, if the user toggles and chooses one of the (say) two types that map to him, the clicking of the command button on that page should lead the user to his unique scenario.

Is this possible in VBA? Would appreciate any pointers on the same. Thanks

Hi,

I have a listbox with the property multiselectextended. I want by default
have one item selected. It only works when I set the property to
multiselectsingle.

Libra_FrmPrintRpt.lbKlas.ListIndex = Libra_FrmLogin.cbKlas.ListIndex
'it works

'Libra_FrmPrintRpt.lbKlas.MultiSelect = fmMultiSelectExtended
'if I decomment this (or set the property in vba-form), it doesn't work
anymore.

Is this a bug, is there a workaround to select by default an item?

Thanks
Jos Vens


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