Vba: Changing Properties Macro On Control Toolbox Item
Jan 25, 2007
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
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 created some excel 2003 spreadsheets to use for Fire/Police dispatchers. I have a series of yes/no option buttons that were created using the control toolbox. I have a macro that clears all the blanks where text is added, but want to add a line that clears the option buttons also.
i have been having this problem with my version of excel at work and it is driving me crazy. First of all this is excel 2000. For a while now I have been unable to use the control toolbox - when I click it - nothing happens. In the meantime I havent been doing anthing really serious so I used the forms toolbar. I have tried using the control toolbox in word - and it works - just in case someone had been messing around and uninstalling things. Is there anyway that I have hidden it somewhere?
I have added a button to asign a macro to but for the life of me cant see how to "activate" the button. I am trying to click the button to make the macro run but it keeps selecting the button like it wont come out of design view.
I've just finished writing some vb code that i need to assign to a button. Trouble is I select the control toolbox and put it design mode and all of the icons with the tool box are greyed out. pic attached below
pukks Auto Merged Post;Doh Doh and double doh!
Problem solved: I inadvertently had selected more than one worksheet tab and this was stopping me from placing a button on the worksheet.
i've gotta demo an Excel app in a few days and this never really bothered me, but someone recently pointed out that it can get distracting for the Control Toolbox to pop in and out randomly. the form is being built for use by other staff, and i suppose it would also be distracting for them if the toolbox arbitrarily appeared here and there. is there a way to turn it off?
I have a spreadsheet with check boxes and a combo box that are set to display in print preview as well as print. I have set up a Print Preview button on this spreadsheet, as well. When you click the Print Preview button and then click close on the preview window and return to the spreadsheet, the control toolbox tools appear out of place and are greatly magnified. Scrolling the screen down and then back makes them appear correctly once again.
I have seen this on this spreadsheet on three different computers. Does anyone have a clue what is going on?
Note, clicking the print preview button on the standard toolbar does not provide the same artifact. It works correctly.
Or for the cheap fix, what is the VB script to move the cursor to a different cell and then return back?
I've started experiencing a strange problem. The control toolbox keeps appearing when the first page on a multipage userform is activated. I can't figure out what's causing this, and after searching the forum, with no luck, I thought I would see if anybody else is experiencing the same problem, and how to fix it.
I am using a form textbox on a worksheet. I have the textbox positioned where I need it to be but when I print the worksheet the textbox moves out of position.
I have an excel workbook where on several of the worksheets I have Control Toolbox Textboxes inserted into the sheet. The Textboxes are for when a user wants to type notes onto the worksheet.
My issue is that the Textboxes do not allow the user to edit the font because the VBA project is locked.
How can I allow users to edit the font in the Textbox without giving the VBA password away to the user?
I've built the code for a Template form for data entry of a survey. Through Control Toolbox I've created the form and used several textboxes, option buttons, checkboxes and COMBO boxes this one in turn being my trouble. - Sheet one is called "Data" (this is the place where all inserted information is going to be stored - Sheet two: "Variables" (here is where I keep the required values for the como boxes - inserted manually apriorely... Question: What is the code to assing to the combo boxes in order to have the values from the sheet "Variables" of the range A1:A4 - use the comboboxq2 for referee, I'll do the others P.S. see the code up to now:
Private Sub CommandButtonN_Click() Dim eRow As Long Dim ws As Worksheet Dim inf As WorksheetFunction
Set ws = Worksheets("Data") Set ws = Worksheets("Variables")
' Find first empty row in database survey eRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ................
I am attempting to put in some dropdown boxes and text boxes and maybe some other items using the control toolbox in Excel. After a selection is made I would like to count that particular item. I was using the validation method in which all I needed was the following to sum up the selections made: =SUMIF(G:G,"Monday",I:I)
Now I realize this will not work due to the fact the control toolbox controls are embeded and merley sit on the cells. How do I sum selections made using drop down boxes that are derived from the control toolbox? If someone can give me an example to try or point me to a place to find such information that would be great.
I'm using combo boxes. Initially I used combo boxes from the Forms toolbar, however the text in the combo box was to small. Now I'm using combo boxes from the Control tool bar. However, i would like the link cell to show the number of the entry in the list (like the forms control box) and not the actual entry. Is there an option I need to select in properties, or is there some VB code I can attach to the combo box ?
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
Can i use for this "live search" in column range any control toolbox or form? only what i want is during writting name it will be shows the results in any textbox or so.
Control box or form can not be programmed so, that after each character check the range and return the results.
which of these toolbars provides the better 'controls' to paste onto worksheets (as opposed to UserForms)? For those wondering, both toolbars contain some apparently similar controls, e.g., combo box, radio button, spinner etc. but there are differences in their behaviour it would seem...
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?
Last weeks I've had too few spare moments to answer questions, hope to pick that up soon. I have a strange problem now with some checkboxes on a multipage control.
Please see the attachment.
I want to check all the 5 boxes on a page if the box above is checked, and vice versa, uncheck them all if the box above is unchecked. I use code of Dave Hawley to loop through the checkboxes.
This doesn't work, but oddly enough, it works if I insert a MsgBox in the code.
I'm trying to give a visual indication to a user of whether a combobox has been correctly filled out.
Basically I want the back colour to be red if the value is "Not Used" and green if the value is anything else.
Normally this wouldn't be a problem, I'd just use the combobox's change event to trigger a check, and adjust the backcolour appropriately.
However, the combobox is dynamically added to the form (depending on options further up the form) - and the particular combobox may not always be created.
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:
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.
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).
While my excel file is protected, I want to have a functionality that allows user to modify on certain areas (of the file), such as below:
1. After the user select number of years at the drop-down list (Main sheet), rows and columns at other related sheets will shift and hide certain values which corresponded from the drop-down list (e.g. if user selects 3 years, then at other sheets, rows from year 1 till 3 will be displayed, and rows for year 4 and year 5 will be hidden).
2. Users can add rows for entering new data at a new line and delete rows for deleting data at the correspond line.
This PC is using the Excel version 2000. At Tools > Protection > Protect Sheet, the available options are 'Content's, 'Objects' and 'Scenarios'. In order to allow users modifying rows/columns (while the file is protected), options such as 'Format Rows' and 'Format Columns' must be checked. But these options are only available in the later versions I believed.
After I have done macro recording for the protection properties at Excel version 2003 (my sister's PC), this is what I got for the .....
I recorded one that does a very simple task, converts formulas to values. I saved it in my personal workbook, all is well. I noticed that excel names them Module1, Module2 etc. I thought it would be better to name them, so in the properties window next to "name" i changed Module1 to ConvertFormulas.
However, when i go to a worksheet, the keyboard shortcut no longer works. Reverting back to the old name Module1 solves this. Clicking Tools -> Macro -> Macros, selecting ConvertFormulas and then Options and modifying the shortcut.
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
