I have problems wit setting some ActiveX controls properties. If I place ActiveX on worksheet manually, it is diferrent object, then If I place it by VBA code. I, am not able to open properities window for controls created by VBA. When I open properties window for ActiveX created manually, there is property fmbackstyle. If I am trying to set this property by VBA for controls created by VBA, result is error.
View 9 Replies
I'm looking for a way to loop through the ActiveX controls (option buttons) in one sheet so that I can have these option buttons reflect what I select on the front sheet.
View 2 Replies View RelatedI created a macro to create command buttons on sheet 1 of my worksheet. The created buttons using oleobject and class type: forms.commandbutton1, creates an object with no properties on the sheet (i.e. if you right click on it excel thinks it's an object and not a control.) Is there a way to create it as a control?
View 4 Replies View RelatedI have a workbook with two worksheets, "Main" and "Control".
Both of them are with hidden tabs (unchecked "show sheet tabs").
On worksheet "Main" I have command button which opens worksheet "Control".
The assigned makro is: Sheets("Control").Select
The problem is: when I open "Control" and close after that the workbook, the next time when I open the workbook "Control" pops-up instead of "Main" even though I do not save it.
1. I need macro on "Main" which will allow me to open "Control".
2. Regardless I "Save" or "Don't Save" "Control" when selected and workbook is closed, to open the workbook always displaying worksheet "Main".
I use Excel 2007 (at home) & 2010 (at office).
I have made an Excel sheet that uses ActiveX controls (Option Box and Check Box). I have included a couple of screen caps at the bottom of this post to better explain the layout. Here are the specifics:
There is one Option Box Group labeled "InputOptionGroup" with choices labeled: "FirstOption", "SecondOption", "RefundOption" and "NoneOption".
Adjacent to the Option Box is a series of Check Boxes that correspond to the choice made in the Option Box with the exception of "NoneOption". The Check Boxes are labeled "FirstCheck", "SecondCheck" and "RefundCheck". Each Check Box object belongs to the group labeled "OutputCheckGroup".
I do know that the value of the Option Box choices and Check Boxes can be either True or False depending on if it has been chosen or marked, but I do not know how to incorporate these values into the action script. With that being said, here's what I'm trying to accomplish:
...when "FirstOption" is chosen, clear values of all Check Boxes and place a check mark in "FirstCheck".
...when "SecondOption" is chosen, clear values of all Check Boxes and place a check mark in "SecondCheck".
...when "RefundOption" is chosen, clear values of all Check Boxes and place a check mark in "RefundCheck".
...when "NoneOption" is chosen, clear values of all Check Boxes (no furthur action).
Also, there are a couple more requests:Is there a way to lock the Check Boxes from user input? I want the Check Box values to be altered only by the choice in the Option Box.
Take the "GPIN" field, the one on the right is locked from user input. This particular cell references user input from the "GPIN" field on the left. Is there a way to return a blank value when the input field is blank insted of returning a "0"? If you notice, all fields on the right reference blank values as "0".
In the left "Amount" field, I need to display a "$" followed by the value in the input "Amount" field
When I try to open a worksheet that has any sort of macros I get the "error opening file" popup with the request to send the error to Microsoft.
When I check the box to "recover and save file" and proceed I am told that repairs were necessary to save the file
The repairs stated are " LOST VBA MODULES" and "LOST ACTIVEX CONTROLS"
I have not changed anything to my knowledge.
Changine the macro security settings to low has no effect either
Here is a sample of code I am having problems with:
Sub CheckBox1_Click()
Range("Make") = Range("Make1").Value
Range("Model") = Range("Model1").Value
Application.Run ("EnterPrice")
Aplication.Run ("Unit Selection")
CheckBox1.Value = True
End Sub
I am having problems with the named ranges "Make" and "Model" which are on another sheet and with runninh the "EnterPrice" and "Unit Selection" Macros which are in Module1, I get the error message "Method 'Range' of object '_Worksheet' class failed"
I want to do disable certain ActiveX toggles depending on the value of a specific cell; this cell can contain more than one value that should disable that control. the logic works as follows:
IF Cell="A" THEN Toggle 1=Disabled AND Toggle 2=Disabled AND Toggle 3=Disabled AND Toggle 4=Disabled AND Toggle 5-Disabled
IF Cell="B" THEN Toggle 1=Ensabled AND Toggle 2=Enabled AND Toggle 3=Disabled AND Toggle 4=Disabled AND Toggle 5-Disabled
IF Cell="C" THEN Toggle 1=Enabled AND Toggle 2=Enabled AND Toggle 3=Enabled AND Toggle 4=Disabled AND Toggle 5-Disabled
IF Cell="D" OR Cell="E" THEN Toggle 1=Disabled AND Toggle 2=Enabled AND Toggle 3=Enabled AND Toggle 4=Enabled AND Toggle 5-Enabled
i have been working on editting a game that i got off the internet and i was having trouble editing the buttons on the worksheet. they dont seem to be linked to a macro and i cant edit them or even delete them
it seems like they are forms but attached to the worksheet.
I have a workbook with multiple sheets. I want to add conditional formatting for a cell on 1 worksheet based on if multiple ActiveX controls are checked (checkboxes) on another sheet. I have 10 checkboxes (now LinkedCells named "G1" -> "G10") on worksheet "Data Entry". The formula should be if all 10 boxes are checked on the "Data Entry" worksheet, cell "A1" on the "Milestone" will be gray. Any clues on how to write the formula for this Conditional Format?
View 1 Replies View RelatedI bought a Surface 2 tablet. When I go to "settings" in the Trust Center it does not show any option to enable Active X.
View 3 Replies View RelatedIn VBA (Excel 2003), I'm wanting to get a reference to an ActiveX comboBox that is inside/attached to a cell.
I can iterate thru all the OLEObjects in the OLEObjects collection of the Worksheet and test the .TopLeftCell property to see if it matches my target cell. However, this seems like a long way around the problem - particularly if there's hundreds of comboboxes in the sheet.
Intuitively, I would have thought there would be a Cell.OLEObjects collection, but it appears that there isn't.
Is there a quicker and easier way to get a reference to the OLEObjects within a target cell?
I have two listboxes on a worksheet and using VBA to load values into the listbox. I also have a combobox that is used to determine what values are to be displayed in the listboxes. I select a value from the dropdown list and depending on the value selected, a list gets populated in the listbox. This works just fine.
The problem I'm having is that when I adjust the zoom on the worksheet, 75% for example, the listbox automatically changes the width when new values are displayed. I try using the listbox1.width = xxx, to keep the width in place, but that doesn't really work.
successfully use Activex combobox on the worksheet, (excel 2003)
without the code from other boxes being triggered at random.
Or should I give this up, and use a form to house these controls? ( I have no Knowledge on this)
I've tried certain IF then statements to protect the excecutable, but it seems, that, even boxes in other books are looked at, with a simple call statement (to a macro in module)
Having a real problem in Isolating these things.
Try to get the code to activate when you select the same Item twice, It seems none of the procedures do this. I made a workaround with using two procedures, but it's a bit clumsy.
How do I reference the value contained within a combobox using VBA?
I have tried the following:
ActiveSheet. OLEObjects("cmbSearchType").Object.Value
ActiveSheet.OLEObjects("cmbSearchType").Value
Sheets("mySheet").OLEObjects("cmbSearchType").Value
Sheets("mySheet").OLEObjects("cmbSearchType").Object.Value
I'm wanting to use the drop-down combo box as a simple way for the user to choose what type of search they wish to perform on the spreadsheet. The search algorithm chosen depends upon the current value show in the combo box.
I am using Excel 2007.
I managed to put the names of my other worksheets into some comboboxes in the main worksheet by using this ....
View 12 Replies View RelatedI have several textboxes that are not on a userform; they reside on worksheet A. I want to format these textboxes so that they only accepts numberical values (like 500,000....no dollar sign and no cents needed). After typing in 500,000 lets say for this example, I want to paste it into a cell in worksheet B (I can actually handle this part). What iIcan't handle is:
Formatting the textboxes so that they only accept numbers (or display a message if a number is not entered) Display the 500000 as 500,000 in the textbox without the user having to enter a comma Hitting enter or tab to get the value from the textbox into the cell in worksheet B. It only pastes it into worksheet B when I click on another cell. That doesn't seem like the right way to enter data.
I am wondering if I just should have used a userform and added the controls onto it. I have 8 sets of 5 user controls; I thought this would be easier.
On a worksheet, I have several commandbuttons. For many of the buttons the name in the "name box" is different than the name in the "(name)" field under Properties.
Researching this, I found this on msdn2.microsoft.com:
An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other isn't automatically changed to match.
So, in my case, one button has:
In the name box: "CommandButton5"
In the (name) properties field: "RunProcess"
The real problem arises when I'm using code to loop through the objects on the worksheet, and build an array of the object names. I'm trying to match the name of the buttons to their click events. In the code, "btn.name" returns the name found in the "name box". The one that I want is the name in the "(name)" field under properties because this is the one that matches the click event.
How can I determine, in code, what is entered in the "(name)" field for an object?
how would i be able to loop through all controls on a worksheet and see what type of control they are? : D
View 9 Replies View RelatedIn my current project, I have set up a worksheet with 15 comboboxes (dropdown lists).
To trigger change events, I use :
Private Sub ComboBox1_Change()
....
End Sub
So, I have 15 such routines defined to cover all comboboxes. By doing so, my code becomes quite large
I am wondering if there is not a more efficient way to handle this.
I have an excel file that has quite a few buttons with macros attached and combo boxes. On one computer it runs perfectly normally, however on another, when the file opens all the comboxes and buttons with macros attached have been stripped from the file.
View 8 Replies View RelatedI would like to know if it's possibale to use VB, to auto manage these levels
IE, 1 box asks for the part number 2nd asks you how many 3rd/4th boxes are "+", "-"
When + or - is pressed the part number will automatically add's/subtracts that part level. I had 10x abcde's I enter 2 in "How Many", then press "-" it will then adjust the level from 10 to 8
I am trying to implement code that can handle a dynamic number of objects, specifically check boxes.
I know of eval( ) in perl that allows one to dynamically create command lines. For instance, the following code would change all of the Checkbox values to 1. The syntax may not be correct but the idea is solid.
Do While i < num_checkboxes
eval("Checkbox" .i. ".Value=1)
Loop
Does this funtion also exist in excel?
I have a userform that stores data (1 row record with 20 columns) to a worksheet.Is there a way to grab the data back in (other or the same)userform, ambent(correct) data and store them back in the same row in the worksheet?
View 2 Replies View RelatedI have used Option Buttons and Combo Boxes on my worksheet. When I print my worksheet, the controls which contain values do not print properly. They only print a fraction of their actual size relative to the rest of the worksheet. The properties are set so that they are printable. What setting do I need to change for them to print properly.
View 2 Replies View RelatedWhen placing a control on a worksheet, the text in the label,option box etc controls is vertical and I can't find a way of changing the direction to left to right. It doesn't happen on all workbooks. I can put the control on a "normal" workbook and then copy it to the "abnormal" workbook but I would like to know why/how I can change the direction of the text.
Under Tools/Options/International the text direction states left to right
I would like to have 4 calendar controls in a worksheet, but even with only two controls I am receiving a Compile Error - Ambiguous name detected: Worksheet_BeforeDoubleClick
View 7 Replies View RelatedI'm currently building an Excel database. This database has a few comboboxes and checkboxes that are tied to each other. If I select the combobox option of "Years", the "Quarters" combobox must be greyed out. In addition depending on which option is chosen, the relevant check boxes must be enabeled or disabled. I've written some code which runs fine, until I try and activate another macro button or if I add and rename a sheet(which is not linked to anything). What should I do? Here is a sample of my code. I have a feeling it's really obviouse but I cant see it..
Private Sub ComboBox2_Change()
If Sheets("InputSheet").ComboBox2 = "Years" Then
Sheets("InputSheet").ComboBox3.Value = ""
Sheets("InputSheet").ComboBox3.Enabled = False
Sheets("InputSheet").CheckBox6.Enabled = True
Sheets("InputSheet").CheckBox7.Enabled = True
Sheets("InputSheet").CheckBox8.Enabled = True
Sheets("InputSheet").CheckBox9.Enabled = True
Sheets("InputSheet").CheckBox10.Enabled = True
Sheets("InputSheet").CheckBox11.Enabled = True
Sheets("InputSheet").CheckBox12.Enabled = True
Sheets("InputSheet").CheckBox13.Enabled = True
Sheets("InputSheet").CheckBox14.Enabled = True
End If...................
I need to make several exact duplicates of an existing worksheet which includes several form controls. Basic Copy/ Paste the worksheet works well but row heights are different and hidden rows are unhidden. Is there a way to make an EXACT duplicate ?
View 2 Replies View RelatedI have a worksheet with lots of things going on... the end user needs access to the Name field, DOB field, all the scroll and form control option buttons in the 9-question sheet, and the macro-enabled reset button at the bottom. Formulas are sprinkled throughout the worksheet, in columns T through AC... option button links go to AA and AB.
I would like to protect the entire sheet so none of the fields, except where indicated above, could be selected or edited... but when I protect the sheet, I get errors when trying to use you form controls, and the formulas do not respond. And finally, when protected, I get a debug error on the reset macro.
