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

Free Microsoft Excel 2013 Quick Reference

Change ComboBox properties in VBA?

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


Post your answer or comment

comments powered by Disqus
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!

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

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,

I tried to change the ShowDropButtonWhen property of combobox 58 in the active worksheet with the following code:


	VB:
	
 Change_ShowDropButtonWhen() 
     
    Set ws = ActiveSheet 
    Dim ComboBox58 As OLEObject 
    Set ComboBox58 = ws.OLEObjects("ComboBox58") 
    ComboBox58.ShowDropButtonWhen = fmShowDropButtonWhenAlways 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Problem is I get a Runtime error '438': Object doesn't support this property or method.
Can you help me solve this problem? Thanks in advance!

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.

Is it possible to change the font size in VBA userform?

I'm specifically interested in changing:
1) the font size for the text in textboxes and
2) the font size of the caption of a label

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

HI,

I need to change one bar color if the value is more than 100%, since so many chart data sheets need to be modified, its huge work to modify those data sheets one by one in conditional chart method.

could any one please give me a solution in vba?

THANKS!

Regards
Max

Hi

Sorry for the earlier post, but hope this is following the roules!

Where do I change the range in this VBA code? The range hase to be E14:G214 as below but it's not working.

Private Sub
Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("E14:G214")) Is Nothing Then
    With Target
    Select Case .Column
        Case 1
            If IsDate(.Cells) And IsNumeric(.Offset(0, 2)) And Not IsEmpty(.Offset(0, 2)) Then
                .Offset(0, 1).Value = .Value + .Offset(0, 2).Value
            End If
        Case 2
            If IsDate(.Cells) And IsDate(.Offset(0, -1)) Then
                .Offset(0, 1).Value = .Value - .Offset(0, -1).Value + 1
                .Offset(0, 1).NumberFormat = "General"
            End If
         Case 3
            If IsNumeric(.Cells) And IsDate(.Offset(0, -2)) And Not IsEmpty(.Cells) Then
                .Offset(0, -1).Value = .Offset(0, -2).Value + .Value - 1
            End If
    End Select
    End With
End If
Application.EnableEvents = True
End Sub


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

Hi!

I would like to know if it is possible to change the properties of a control in a form at design-time, using code or other.

The problem is that I have lots of TextBoxes in a form and every time I add a new one, I have to change the MaxLength property from 0 to 4.

I know I can do this at run-time, but I don't want to run unnecessary code since this is a default property.

I also know I can copy-paste every control with the same properties but I've had to change them afterwards, from 4 to 3, which is not easy since I have textboxes in different frames and also different pages in a multipage control.

Thank you for your help!

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 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.

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!!

Is there a way to have a code modify itself in different checkboxes?
I has a lot of checkboxes, and with each box i have to modify the cells that it corresponds to, and the number of the check box.

This is my formula for checkbox 10.

Private Sub CheckBox10_Click()
Application.ScreenUpdating = False
Sheets("Inventory").Unprotect ("8790406")
If CheckBox10.Value = True Then
Range("A13:k13").copy Sheets("Sold").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Rows("13:13").Select
Selection.delete Shift:=xlUp
End If
Sheets("Inventory").CheckBox10.Value = False
Sheets("Inventory").Protect ("8790406")
End Sub

in checkbox 11 i have this.

Private Sub CheckBox11_Click()
Application.ScreenUpdating = False
Sheets("Inventory").Unprotect ("8790406")
If CheckBox11.Value = True Then
Range("A14:k14").copy Sheets("Sold").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Rows("14:14").Select
Selection.delete Shift:=xlUp
End If
Sheets("Inventory").CheckBox11.Value = False
Sheets("Inventory").Protect ("8790406")
End Sub

I have been copy and pasting between each checkbox code, and then changing the checkbox # in the two areas, and the cells that they correspond with. I want to know if there is a way i can have the checkboxes automatical, or with little work, have the correct checkbox # and cells.

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??

Dear All,

I have what i expect to be a simple question but after hours of trying seem to be hitting the same problem.

I have written a module in VBA which is using IF Then statement and if the criteria is true i want to give a particular cell a value please see below for an example of my code.

Function CheckCell(Cell_Value As
String)

If Cell_Value = "Example" Then
         Range("B22") = "hello"
    ElseIf Cell_Value = "Example 1" Then
         Range("B22") = "hello1"    
    Else
         Range("B22") = 0
End If
    
End Function
When this function runs i just get #value in cell B22

I have tried the following Syntax instead of

Cells(B,22).Value = "Hello"
Activeworkbook.sheet(1).range("B22) = "hello"
Etc etc

But nothing seems to work.

Any help much appreciated.

Can anyone help with this problem:

I am trying to write a macro to change a formula in a cell and place the new
formula in the same cell.

For instance:
Cel L1 in a worksheet contains: ='C:TEST[file.xls]data'!J7
By hand I can change this in: = TRIM('C:TEST[file10.xls]data'!J7) which
removes excess spaces.
How must I do this in a macro?

Thanks in advance

MahrYon

Hi,

I have the following situation in Excel:
I have a worksheet called "Begroting Calc" with comboboxes to select an option from a list on another sheet called "Rekenblad uitgangspunten Calc". I have created a macro which makes a copy of both sheets and renames these sheets to "Begroting WVB" and "Rekenblad uitgangspunten WVB". It also copies the comboboxes and formulas on these sheets.
The problems are:
1) I have 224 comboboxes and by inserting a row in worksheet "Begroting Calc" (the copied worksheet) it will be 225. So the amount of comboboxes can change. The macro will have to search for all comboboxes in the worksheet "Begroting WVB" and change the LinkedCell-properties from "'Rekenblad uitgangspunten Calc'!D..." to "'Rekenblad uitgangspunten WVB'!D..." and the ListFillRange-properties from "'Rekenblad uitgangspunten Calc!C...:C..." to "'Rekenblad uitgangspunten WVB'!C...:C...". I tried to create a macro myself, but it will only change the named comboboxes and uses a lot of space (see below).
2) In cells M12 to M224 of worksheet "Begroting Calc" I have these formulas:
='Rekenblad uitgangspunten Calc'!F3
='Rekenblad uitgangspunten Calc'!F6
='Rekenblad uitgangspunten Calc'!F9
and so on...
These formulas will also have to change to:
='Rekenblad uitgangspunten WVB'!F3
='Rekenblad uitgangspunten WVB'!F6
='Rekenblad uitgangspunten WVB'!F9
and so on...
But by inserting a row in "Begroting Calc" (the copied worksheet) the range will be M12:M225. So the macro will have to search for all cells in worksheet "Begroting WVB" with the formula in it.
Can someone help me with these problems? Thanks in advance!

Sub ChangeComboBoxProperties()

Dim ComboBox1 As OLEObject
Dim ComboBox2 As OLEObject
Dim ComboBox3 As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set ComboBox1 = ws.OLEObjects("ComboBox1")
With ComboBox1
.LinkedCell = "'Rekenblad uitgangspunten WVB'!D3"
.ListFillRange = "'Rekenblad uitgangspunten WVB'!C3:C5"
End With

Set ComboBox2 = ws.OLEObjects("ComboBox2")
With ComboBox2
.LinkedCell = "'Rekenblad uitgangspunten WVB'!D6"
.ListFillRange = "'Rekenblad uitgangspunten WVB'!C6:C8"
End With

First, this site is a great resource. Everytime I get the feeling I'm getting good at VBA I only need search some threads before realizing I have a long way to go!

My question is regarding changing properties in a chart without activating it. My current example is with adding data labels, but there are many other instances I could use this information in. Here is my current code:


	VB:
	
ActiveChart.SeriesCollection(2).ApplyDataLabels 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, I was hoping to replace it with something like this:


	VB:
	
Sheets("Dashboard").ChartObjects("Chart 1").SeriesCollection(2).ApplyDataLabels 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Alas I get the "Object doesn't support this property/method" error. Is there a way to do this? It just seems inefficient to have to activate the chart in order to make changes. Any help/advice would be appreciated. Thanks.

Hi Everyone ,

I have 2 spreadsheets (attached) I need to pull gross salary info from one to another by using EmployeeID. I want to use Vlookup function in VBA However I'm totally VBA Newbie. I have found a code but it does not make sense to me now. I was wondering could you please help me to make necessary changes to get it to work?

Sub TestLookup()
Dim FileName As String
FileName = "C:yelizgross salary.xls"
adoLookup FileName, ActiveSheet.Cells(2, 1)
End Sub

Sub adoLookup(FileName As String, luValue As String)
Dim adoRs As New ADODB.Recordset
Dim adoConn As New ADODB.Connection
Dim query As String
Dim col As Integer

With adoConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & FileName & ";Extended Properties=Excel 8.0;"
.Open
End With
query = "SELECT * FROM [Sheet1$A1:D6000]"
With adoRs
.CursorLocation = adUseClient
.Open query, adoConn, adOpenStatic, adLockReadOnly, adCmdText
.Fields(0).Properties("Optimize") = True
.Find "EmployeeID = '" & luValue & "'"
If Not .EOF Then
For col = 1 To 3
ActiveSheet.Cells(5, col + 1) = .Fields(col)
Next
Else
MsgBox "Employee " & luValue & " could not be found.", vbOKOnly, "Invalid ID"
End If
End With
End Sub

Regards
Yeliz

Hello Guys ,

I' trying to change code in VBA , my problem is i want user to enter data in Sheet(GJ Entry)
and merge it to General Journal without the user choose Data Sheet Name in Column (C), i want to remove this column (C) in Sheet (GJ Entry), i want it send directly when user press Post Data button

Excel file Attache

Thanks For help

Hello,

I am trying to use the "ThisWorkbook.Name" property for the workbook name in Vlookup

I previously used the following which worked -


	VB:
	
ActiveCell.FormulaR1C1 = _ 
"=VLOOKUP(C[-1],'[myworkbook.xls]Sheet1'!R1C2:R100C2,1,FALSE)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I change the workbook name often so I figured instead of constantly changing the name in the vba code, why not use
ThisWorkbook.name...unfortunatley it does not work when I do the following...


	VB:
	
ActiveCell.FormulaR1C1 = _ 
"=VLOOKUP(C[-1],'[ThisWorkbook.Name]Sheet1'!R1C2:R100C2,1,FALSE)" 

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

Any suggestions on this would be appreciated.

Thank you!

I need to print a form but the printer orientation must be landscape.
I'm not the only one that uses this excel file and it is used in several computers.
How do i change the orientation whith vba code?

Ex:
With a commandbutton "Print" be able to change the orientation and then print

thanks


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