Excel 2010 :: Change ActiveX Properties

May 29, 2013

It has been awhile since I've worked on anything and I can't remember how to do something that is probably very simple. I have placed an option button on my worksheet and cannot for the life of me remember how to change the properties. When I right click and select properties a box pops up but there is no info there. I want to be able to change the name and group etc.

View 3 Replies


ADVERTISEMENT

Can't Change Combobox Properties (activex)

Apr 24, 2009

How do I set the font size property and special effect property in this code?

I was trying to answer a question on Experrt Exchange but Rory beat me to it and now I'm feeling frustrated that I can't get my solution to work.

Sub AddCombo()Dim rVals As Range, rCell As Range, lTop, lLef, lHeight, lWidth, lCount As LongSet rVals = ActiveSheet.Cells.SpecialCells(xlCellTypeAllValidation)lCount = 1For Each rCell In rVals    If rCell.Validation.Type = 3 Then        lTop = rCell.Top        lLeft = rCell.Left        lHeight = rCell.Rows.Height        lWidth = rCell.Columns.Width            With ActiveSheet.OLEObjects.Add(classtype:="Forms.ComboBox.1", Left:=lLeft, Top:=lTop, Width:=lWidth, Height:=lHeight)                .Name = "NewCombo" & lCount                .ListFillRange = rCell.Validation.Formula1                .LinkedCell = rCell.Address(0, 0)                '.SpecialEffect = fmSpecialEffectFlat //doesn't work //                '.FontSize = 14 //doesn't work //                '.Font.Size = 14 // doesn't work //            End With                        lCount = lCount + 1    End IfNext rCellEnd Sub

View 9 Replies View Related

Macro To Change Table Properties In Outlook 2010

Jun 16, 2013

The macro im using copys a table from excel into a email. Unfortunatly the email is not readable for blackberry users due to the table properties.

I need to change a few properies. This is what i do manually.....

In outlook i then hover over the table right click then select "Distribute rows evenly" then i right click again but this time i select "Table properties" i then go to the "Row" Tab

i then untick the "allow row to break across pages"

then tick "specify height" and change the value to 0.4 cm and finally change "row height is" to "exactly"

Below is the whole code im using so far....

Code:
Sub Mail_Selection_Range_Outlook_Body()
Dim rng As Range
Dim OutApp As Object

[Code]...

View 2 Replies View Related

Excel 2003 :: How To Change Properties Of File

Jun 1, 2013

I have a question regarding the properties of an Excel file (.xls 2003). I can change them all (as indicated on Microsoft support website) expect the name of the last person who recorded the document (File ==> Properties==>Stat.)

How to change the name?

View 2 Replies View Related

Excel 2010 :: Macro To Change Excel Formatted Table Name Dependent On Number Of Loops

Jul 9, 2012

Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).

I am using excel for windows 2010. The macro that i have so far is below.

Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value

[Code] ........

View 1 Replies View Related

How To Change The Properties Of A CheckBox Using VBA

Aug 4, 2014

I am trying to use VBA to change the caption of checkboxes in "Sheet 2" when I change the value of a cell "A1" in "Sheet 1".

This code is working:

Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub

Worksheets("Sheet 2").CheckBox1.Caption = "New Caption"End Sub

But there are 6 checkboxes in Sheet 2 and I would like to do something like this:

Private Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then Exit Sub
For i = 0 to 5Worksheets("Sheet 2").Control("CheckBox" & i+1).Caption = "Box" & i+1Next iEnd Sub

This doesn't work.. So I guess the Control-function is wrong.

View 3 Replies View Related

Change Textbox Properties

May 25, 2007

I am having a hard time setting the MultiLine, Scrollbars and WordWrap properties through programmation. Actually what I want my code to do is to create a set of textboxes in my spreadsheet and then set the MultiLine, Scrollbars and WordWrap properties. Right now I can create the boxes and rename them but I'm unable to change the other properties.

inst_num = Range("AA1").Value

ActiveSheet. OLEObjects.Add("Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=20, Top:=95 + (inst_num - 1) * 105, Width:=70, Height _
:=30).Select

ActiveSheet.OLEObjects.Add(ClassType:="Forms.TextBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=100, Top:=95 + (inst_num - 1) * 105, Width:=100, Height _
:=30).Select.............

View 2 Replies View Related

Excel 2010 :: Change Color On If And Then Statement

Nov 20, 2013

I tried Conditional format and no luck. (Office 2010)

What I'm trying to do VB is:

If Cell B:B has value of "402" or "up"
and Cell C:C is less >100
Then Change color on Cell C:C to "yello, red, etc" (if C value is <100 no color change)

View 6 Replies View Related

Combo Box To Change The Properties Of A Text Box

Oct 9, 2009

i am trying to use a combo box to change the properties of a text box. i think i can do this by having it look at the cell link that i sent and then changing the text box appropriately with something like this:

View 3 Replies View Related

Excel 2010 :: Change Font Size In A Checkbox?

Jan 30, 2013

How do you changethe font size in acheckbox in excel 2010?

View 1 Replies View Related

Excel 2010 :: Change Several Cells If Statement Is True?

Oct 11, 2013

I am using MS Excel 2010 want what to change a number of cells if a value is true e.g.

Row one has the headings Prz, Stat, Val, andPts
Row two has the following values :-
(Prz) a2 =2, (Stat) b2=1, (Val) c2=$30, (Pts) d2=10
How do I do the following in Excel :-
IF B2=1 THEN A2+1 AND C2+30 AND D2+5
so that the above cells are changed to
(Prz) a2 =3, (Val) c2=$30, (Pts) d2=10

View 4 Replies View Related

Excel 2010 :: How To Change The Background Colour In A List Box

Nov 5, 2013

How do i change the background colour in a list box?

An example is attached.

View 2 Replies View Related

Excel 2010 :: Pivot Table Change Event

Mar 22, 2012

I have three worksheets Sheet 1, Sheet 2 and Sheet 3 with three Pivot Tables

Sheet1 - Sheet1Pvt1
Sheet2 - Sheet2Pvt2
Sheet3 - Sheet3Pvt3

When I use In Sheet 2 the following lines of code

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Application.EnableEvents = False
Application.ScreenUpdating = False
MsgBox "The pvt table refreshed " & Target.Name
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

and even If I Refresh Sheet 3 pivot table, Sheet 2 Event is triggered and it prints "The pvt table refreshed Sheet2Pvt2";

How to restrict the event code only to Sheet 2 and pvttbl Sheet2Pvt2.

View 3 Replies View Related

Excel 2010 :: Change Pivot Table Source With VBA?

Nov 25, 2013

I am using Excel 2010 and have a workbook with two sheets - "Risks & Issues" which contains the source data and "Risks - Summary" which contains a Pivot Table called "Dashboard"

I would like to create a command button called 'Refresh Data' which will be on the "Risks - Summary" sheet. When I add a new line to the "Risks & Issues" sheet, I would click the button in "Risks - Summary" and it will update the Pivot Table range.

So far I've tried using some examples found on this site, although with no experience in VBA macros, I'm not really sure what they do; all I know is that they cause an error.

Code:

Sub Refresh_Click()
Sheets("Risks - Summary").PivotTables("Dashboard").SourceData = Sheets("Risks & Issues").Range("A2").CurrentRegion.Address(True, True, xlR1C1, True)
End Sub

[Code].....

View 1 Replies View Related

Excel 2010 :: Automatically Change Value Based On Date?

Apr 18, 2014

I'm relatively new to Excel and I'm currently making a basic spreadsheet for my personal income/spending.

How would I make the "Earned this Month" and "Spent this Month" tabs in the top update on a month to month basis automatically?

For example, in the month of may, it would display may's values, june would be june's etc. etc.

(Excel 2010 w/ Windows 7)

View 3 Replies View Related

Change Values Of ActiveX Checkbox Within VBA

Jun 3, 2014

I have a sheet with many active x check boxes already created but with visibility off. I want code that turns on the visibility, repositions, and checks certain check boxes whose name is based on values in an array named configs.

Here is the code with the problem line indicated:

[Code] ........

The repositioning and visibility work fine. I get an "object doesn't support property or method" error on the indicated line. What is the proper syntax to do this?

View 4 Replies View Related

ActiveX Comboboxes Change To Icon

Jun 20, 2007

I have one sheet with about 100 activex controls (and another few 100 on the other sheets in the file), most combobox, but some checkbox as well. There are 30 of them lined up, one in each row. One at a time, one control will basically change to an icon only. If you select it in design mode, the name shows in the range name area of the toolbar, but when you select properties you get the properties for the sheet. Effectively I have an icon with nothing linked to it anymore.

For a while, it seemed to be somewhat random. But for the last few days, it is now cascading down. First combobox #6, then #7, etc. I'm now on #11 or so. Seems to happen when you save then reopen the file, not if you just keep working in the file without closing it.

The macros behind the boxes are still there and run once I recreate the missing box (normally with an Alt-D, and editing the properties; but I've also created from scratch from the toolbar, and used alt-C, alt-V) they work fine. I've run a compile, with no change. Any thoughts on finding the root cause? I thought it might be from blowing up a macro, but I've tried it without even editing a macro and it has the issue.

View 4 Replies View Related

Change Form Button Properties With A Macro?

Nov 12, 2008

I have a button wich will add data entered on a form to a spreadsheet if the data meets certain criteria. If it does not it will not let you add anything. I would like to add a second button that will add the data even if it does not meet the criteria but I need the second button to only be enabled with a password. Maybe a macro to change the button properties from locked to unlocked (true to false).

View 2 Replies View Related

VBA - Loop Through All Images On Sheet Change Properties

Jun 18, 2013

I need some code that will loop through all the images on a spreadsheet, and change the properties so the images will "Move and Size with Cells"

View 2 Replies View Related

Excel 2010 :: Change Style For Certain Number Of Lines In Word Doc From VBA

Apr 29, 2014

i have done in Word VBA successfully (basically, change the Style for a certain number of lines):

VB:
Dim myRange As Range
Set myRange = ActiveDocument.Range(ActiveDocument.Range.Start, ActiveDocument.Range.Start)

[Code].....

View 2 Replies View Related

Excel 2010 :: Change Cell Color Per Greater Than / Less Than Formula?

Aug 30, 2013

My formula is =IF((C2+E2)>D2,"WIN","LOSE") the new formula if I wanted the cells with "WIN" to be green, and the 'LOSE' cells to be yellow. Excel 2010

View 6 Replies View Related

Excel 2010 :: VBA Change Cell Formatting Of All Highlighted Cells

Jul 25, 2012

I'm using windows xp and excel 2010. I have a very large sheet that has a bunch of highlighted cells. There all highlighted the same color. I need to remove the highlighting colors and change the font color of the highlighted cells to red.

View 3 Replies View Related

Excel 2010 :: Change Spacing Between Dates In Pivot Table?

Mar 27, 2013

I have a pivot table with dates along the x-axis. The data is spread out over 5 years and the spacing between sampling events is not even, therefore, the spacing of my x-axis dates is not even. I want the x-axis to look similar to a normal graph where there is equal space between each month, even if I don't have data for each month. I do not want to group the data to accomplish this. If I click the "show items with no data" it only shows one extra day, not all days or even all months. I have excel 2010.

View 2 Replies View Related

Excel 2010 :: Automatically Change Text Entries To Set Number ID / VBA?

Jul 24, 2014

Basically I first want to be able define around 100 variables such as:

Dog = 1
Cat = 2
Mouse = 3

And so on. The reason being my final data needs to be the numbers for it's purpose but because there are so many I can't possibly remember them all to just type out 23 | 44 | 76 | 2 etc. What I would like to then happen is that whenever I type out the words in a cell it's automatically replaced with the number ID.

So typing in

Dog
Mouse
Mouse
Cat
Dog

would automatically result in

1
3
3
2
1

In those cells when I hit enter or at the end in one go. Is this at all possible in Excel 2010?

View 1 Replies View Related

Macro To Change ActiveX Label Caption?

Mar 2, 2014

I needed to put a Checkbox on a Worksheet. The standard Checkbox options in Excel FormControl and ActiveX Control were too small. I found a work-around using the following macro attached to an ActiveX Label that was formatted as Wingdings font. The macro basically changes the character from a empty box Wingdings Chr(168) to a checked box Wingdings Chr(254) when the user clicks on the label.

Private Sub Label1_Click()
If Label1.Caption = Chr(254) Then
Label1.Caption = Chr(168)

[Code]....

But I get this error on an ErrorHandler that I have included in the macro. "An error has occurred. Error number =438. Error Description=Object doesn't support this property or method."

View 3 Replies View Related

Activex Combobox Change Font Size - VBA

Sep 13, 2012

I am trying to change the font size on my combo box using VBA.

I create the combobox dynamically, so need to be able to change the font too.

This is how I create the combobox, however the .Font.Size = 10 does not work and the default font size is 11.

Code:
With ActiveSheet .OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=159.75, Top:=80.25, Width:=75.75, Height:=19.5) _
.Font.Size = 10
.Name = "cmbBaseD"
.OLEObjects("cmbBaseD").ListFillRange = "DynRng"
End With

View 2 Replies View Related

VBA - Change Caption Of ActiveX Checkboxes Using A Loop

Jan 24, 2013

I am trying to change checkboxes caption name using a loop so that I don't have to change them individually. Here is a simplified example of what want to do:

1) I save the caption names in a array
2) I run through a loop to rename each checkboxes using the names saved in the array

Sub change_Checkbox_Caption
Dim MyArray(1to3) as string
Dim IndexMyArray as integer
MyArray(1) = "YES"
MyArray(2) = "No"
MyArray(3) = "Maybe"

[Code]...

View 2 Replies View Related

Option Button Properties Change Based On A Checkbox

Jun 13, 2006

I would like to set up some option buttons so that the will be become visible and enabled when a check box is checked, and the opposite when the same checkbox is not check. This is my problem

Private Sub cbpDiscAlum_Click()
Dim myOption As Control
Dim myValue As Boolean
myValue = cbpDiscAlum.Value = True
If myValue = True Then
For Each myOption In pDiscounts.Controls
myOption.Visble = True
myOption.Enabled = True
Next myOption
Else
For Each myOption In pDiscounts.Controls
myOption.Visible = False
myOption.Enabled = False
Next myOption
End If
End Sub

It is the 5th command where it gets hung up: For Each myOption In pDiscounts.Controls. I am sure I am spelling both of the names correctly: the checkbox, and the group name.

View 3 Replies View Related

Change Properties Of Controls On Ribbon Toolbar. 2007

Feb 26, 2008

I've created a custom ribbon which contains checkboxes that hide or show columns.

I would like to change the enable state of the checkboxes while the user is interacting with the spreadsheet.

For example:I would like to disable all of the checkboxes if they are not viewing sheet1I would like to disable the second checkbox if the first checkbox is unchecked.

I know how to set these all on initialize. I just have not figured out how to update these controls after it has been opened.

View 4 Replies View Related

Excel 2010 :: Cannot Change Chart X-axis To Display Date Values

Nov 27, 2012

Example attached : Copy of CAB.xlsx

View 2 Replies View Related







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