Just recently changed computers (got this one from a colleague) and I have a strange problem. Within a userform, for example when wanting to change the color of a text label (uses the forecolor property), when I open the properties box and click on the arrow that normally shows me the color pallette, I get nothing. This is true for all properties where I know for a fact one can change the color within the properties for that control. I've no idea why this is, but it's true for any spreadsheet that I open/create on this computer.
how to get a list of the properties for form control shapes (not control toolbox shapes) that are placed on a worksheet (not on a userform). Eg., a button, checkbox, combobox, etc.
If it's possible, I'm interested in working with properties like "enabled", "caption", etc. that aren't listed on the "Format Control Properties" dialog.
I understand you can edit properties of a form control shape via VBA code (See example below), however, I can't seem to find anything within the object browser about them.
EXAMPLE With ActiveSheet.Shapes("Scroll Bar 2").ControlFormat .Min = 10 .Max = 150 End With
I have a bunch of Option Buttons in a worksheet whose properties i need to change by running a macro. For example, I created an OptionButton1 in a worksheet using control toolbox. Now I want to run a macro to change it's name, groupname, linkedcell and caption. I did this macro but it doesn't work:
Sub Label() Dim Code1 As String Dim Link1 As String Dim Form1 As String Dim GroupName1 As String Dim Caption1 As String Worksheets("Section1").Select Form1 = "OptionButton1" Code1 = "FButton1" Link1 = "FLink1" 'Defined in worksheet GroupName1 = "FGroup1" Caption1 = "Choose Function 1" With Worksheets("Section1").Form1 .Name = Code1 .LinkedCell = Link1 .GroupName = GroupName1 .Caption = Caption1 End With End Sub
I want to access the Properties of a number of controls in a running form, and these controls may or may not be contained in a Frame or a MultiPage.
In particular I want the Top and Left for these controls, which means I have to first find out if the control is contained in a Frame or MultiPage so I can get the reference for Top and Left. I'm ok with doing this for controls inside a Frame, but the MultiPage is eluding me. I get an error when I try to access these controls and it looks like they are actually owned by the individual Pages of the MultiPage.
how do I find out if a given control is contained in a given MultiPage?
Im trying to create many option buttons on my sheet and then change the GroupName and Name properties but im having no luck in accessing the properties. Here is the code ive got at the moment, could someone tell me how to access these properties. Shown below is a small section of the code, if you require the full code i will happily provide it. This is my first post and im not sure if this code tag thing has been done properly:
n = 18 Call Section_Header Range("D" & n - 1).Value = " Case" For i = 1 To UBound(Name_Case, 1) Range("D" & n + i).Value = Name_Case(i) Range("M" & n + i).Value = Cost_Case(i) ActiveSheet. OLEObjects.Add(ClassType:="Forms.OptionButton.1", _ Left:=628, Top:=(n - 1) * 15.75 + (1 * 6) + (i - 1) * 15.75, Width:=46.5, Height _ :=15.75).Select Next i
I have text boxes in excel that hold text (obviously) over a period of several days. It gets updated daily, so I want to change the font color for the new text that is added daily rather than all the text. Is there a way to do this?
Also, is there a way to get the verticl scorll bar to always show on the right hand side of the text box? The way it is now, I have to click once or twice in the text box before it shows up. I would like the person reading it to automatically know that there is text not presently in view so they know to scroll down for more information.
I have a problem with combobox, when an item in a combobox is selected (control tool box combobox) then the color of the cell in excel worksheet have to change to respective given color
I am using the following code to create an Excel toolbar custom control Button. What would be the VBA code to change the color of the image I have chosen? ....
I have an Excel 2010 workbook with many worksheets using hundreds of Form Control (not ActiveX) checkboxes. I need a bit of VBA to change Checkbox background color of each checkbox whenever the user checks the box. I assume this needs to be a click event? I don't even know the Checkbox property name I need to change I'm learning VBA as quick as I can
Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?
I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.
I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.
I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).
How do I determine which control the user is currently modifying on a multipage form (either changing, enterying or exiting the specific control). when I use "userform1.activecontrol" i get "multipage1" as the control name but I need the actual control on the specific active multipage. (also the .TABINDEX is for the multipage regardless of the on-page control) I use a generic data-field change SUBroutine so need the control name (and the TABINDEX) to provide my SELECT CASE. (so every fieldname_CHANGE calls the same SUB [with no parameters])
Although the question I'm about to ask is not related to Excel, but related to MS Word, the coding is similar.
The above code I used to set the properties value, but I get an error highlighting 'storedPath'. I speculate MS Word does not recognized this word. Is there another word or code that I can use to set the value in the MS Word document properties?
Is it possible to disable The "Look In:" field of the GetOpenFilename dialogue? What I would like to do is to keep users from selecting folders other than the CurrentDirectory settings and if possible to keep the user from deleting,copying and pasteing to the files in the current dirrectory displayed. The code I have is:
I would like to programatically add information to an Excel file's Properties, the Details tab. I have alot of files in the applicable group. Files are .xls but I'm using Excel 2007.
Is there a way to set the printer's properties using VBA?
Sometimes we set the printer for BEST quality to do photos or brochures. Well if we don't change it back, when we go to print a spreadsheet, it takes forever because it is set on best quality.
So...... I want to be able to set the print properties to normal using VBA.
I have a problem with the PivatTable properties in Excel and VBA. The problem is as follows:
This is a example table:
Sum of store_sales time_id product_id store_id 367 368 369 1 3 6 7 11,4 11 13 14
(the format is not real clear, but I will explain)
This is a part of a PivotTable where: Sum of Store_sales is located in the datafield(one record; 11,4) product_id and store_id are Row-Items Time_id is a column item.
Now, I want the properties of the cell containing 11,4. I've made it so far in VBA that I can ask what his column-items and his row-item are.
Column = Application.Range(chosenCell).PivotCell.ColumnItems.Item(1) Row = Application.Range(chosenCell).PivotCell.RowItems.Item(1) Row2 = Application.Range(chosenCell).PivotCell.RowItems.Item(2) But how do I get VBA to return the valueheaders of those columns and rows? So actually, I want VBA to also return the names: product_id, store_id and time_id. This is because I need those headers to create a query which I send to a Access database.
Is someone able to give me a hint? Is there a method for this in VBA?
I have a workbook with 10 worksheets and I need to know the memory size for each worksheet. I know from File/ Properties that the file is 3.7mb but that is much higher than I would have expected. I can't tell which worksheets are causing it to be so large.
2. Use the Offset and End properties of Range object to name range from A2 to the end of the column as “NEmployees”, range B1 to the end of the row as “NScores”,and the rest of the range, B2 to F19, as “ScoreData”.
3. Do some formatting using the range names and the With-End With construction: make the font of the NEmployees Range in bold and blue color; change the font of the NScores Range to italic, in red and centralize the text (using the HorizontalAlignment property).
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.
I have a button on a worksheet that activates a macro, the macro is stored in a "personal.xlsb" file. This file is copied to several users computers so they can use the macro, problem is once the button is assigned to a macro from one computer all the other users can't use the macro. What can I do to make this macro work on all computers? (Less placing yet another button on the tool bar).
