VBA Commands For ActiveX Controls

Jul 24, 2009

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

ADVERTISEMENT

Deleting VBA Modules And ActiveX Controls

Sep 5, 2008

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

View 9 Replies View Related

ActiveX Controls On Worksheet - Fmbackstyle

Feb 16, 2009

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 View Related

Running Macros And Activex Controls

Jun 10, 2009

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"

View 9 Replies View Related

Conditional Disabling Of ActiveX Controls

Feb 28, 2010

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

View 9 Replies View Related

Loop Through ActiveX Controls In Worksheet

Jun 22, 2006

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 Related

ActiveX Sheet Controls Basics

May 3, 2008

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.

View 3 Replies View Related

Conditional Formatting For Multiple ActiveX Controls

Aug 11, 2014

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 Related

Excel 2013 :: Enabling ActiveX Controls

Apr 4, 2014

I 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 Related

Programmatically Create ActiveX Worksheet Controls

Apr 2, 2008

I 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 Related

Excel 2003 :: VBA / Reference ActiveX Controls In A Cell

Oct 12, 2011

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

View 2 Replies View Related

Excel 2007 :: ActiveX Controls Command Button Opening Worksheet In Same Workbook

Feb 6, 2014

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

View 1 Replies View Related

VBA - How To Use Add-in Commands

May 12, 2012

How I use the Private Subs for Workbook_AddInInstall and Workbook_AddInUninstall with my addins and confirm it is nececessary to use them?

For 2 weeks, I have not been able to get my addins to activate and load into the AddIns tab. I think these missing Subs are why. Ironically, Every example on how to create addins never mention this step or it being necessary. I've even looked in Macros book and it does not mention anything about needing these subs in the Creating AddIns section so they will show up in the AddIns Tab.

How I can get my Addins in the tab and not the Quick Launch menu

View 8 Replies View Related

Commands And Toolbars Gone

Jan 30, 2008

I lost my command bar and tool bars (excel 97) all I have is the blue bar on top then the column letters. How do I restore them?

View 9 Replies View Related

MIN & MAX VBA Commands Not Seeming To Work

Dec 4, 2009

I would like someone to explain the reason why when the program is run, the maximum value in column S successfully is selected by being colored in pink, but the minimum in column R between those pink max's (indicated by the entire row outlined in red) is not the minimum.

In other words, look at the numbers in column R. Only the 14.08 rows are supposed to be boxed in red. I can't understand why the 30.08 and the 46.08 are boxed in red??? And puzzling to me is why the 46.08 near the top are boxed, yet the 46.08 near the bottom are not boxed.

View 7 Replies View Related

Executing Two Different Commands With Same Button

Dec 17, 2008

I just want to have a column sorted by Z-A order and then A-Z by the same button, I recorded 2 macros on 2 buttons and tried to join codes with "if, elseif".

View 6 Replies View Related

Open And Enter Commands

Feb 24, 2006

Is there any way that i could open and enter commands in ms dos
controlling from excel vba code?, like, say i wanted to change the current
directory in msdos from d: to c:, is there anyway that i could do it from
excel vba?

View 14 Replies View Related

Disabling All Ribbon Commands

Jan 9, 2014

I have a template sheet where the only thing the user need to do is fill in all the appropriate cells and the click the button that says SAVE to save the file to the right folder and file name.

I want to Grey out the ribbon completely so that user can even change the font or any thing else for that matter. The sheet and workbook is protected. Need a way to stop any unwanted tinkering.

View 3 Replies View Related

Missing Menu Commands

Oct 29, 2008

I have run into a problem with the Insert menu.

In the submenu
Cells
Rows
Columns
Worksheet

are "Greyed out" and therefore not available.

This happens when the application is first opened - no code is running it seems to be associated with the Personal workbook?

I have not encountered this previously.

IT help desk was its usual high standard - ZIP

I have searched this board without success.

The Microsoft Help functions were also silent.

View 9 Replies View Related

VBA Macro- Apply Set Of Commands

Jan 31, 2009

Sub elaseval()
For each cell in Range(B7:B50)
Range("cell.Value").Select
Selection.Copy
Sheets("Input").Select
Range("$E$7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sensitivity").Select
Range("C & cell.Row : T & cell.Row").Select
Selection.Copy
Range("C" & Cell.Row).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next Cell
End Sub

I am trying to apply this set of commands to each cell in the range B7:B50. For each of these cells, I need "cell.Value" to refer to the value in the respective cell and "cell.Row" to represent the row of the cell that is currently being used.

View 9 Replies View Related

Removing File Commands

May 2, 2009

I am trying to remove File Commands from this specific workbook and force save and send functions through command buttons.

This is the code I tried in the workbook module to remove file commands(Excel 2003):
__________
Sub Workbook_Open()
Dim a As CommandBar
For Each a In Application.CommandBars
Application.CommandBars(a.Name).Enabled = False
Next
Application.CommandBars("File").Enabled = False
Application.CommandBars("Edit").Enabled = False
Application.CommandBars("View").Enabled = False........

View 9 Replies View Related

Disable Menu Commands

Jun 1, 2007

I need to disable the "save as" file menu command when a user opens a shared document. Upon exit I want to enable the save as so that other opened worksheets are not affected. This code works: CommandBars("File").Controls("Save As...").Enabled = False

My problem is this. I can activate this command by selecting run in the VB editor.
But I need for this action to be automatic when a user opens the worksheet. I did this in the editor:

Under general

Sub dISABLESAVEAS()
CommandBars("File").Controls("Save As...").Enabled = False
End

Sub WORKSHEET_BEFORECLOSE()
CommandBars("File").Controls("Save As...").Enabled = True
End

View 2 Replies View Related

Nesting Multiple Commands Into A Formula?

May 13, 2014

I want to accomplish a few things in one cell. The basics are this:

I want to pull the lowest numbers from a given sample. The sample needs to have a time component (10 lowest out of the most recent 20 results). Those numbers are to be averaged. That average is to be multiplied by .96, then that final yield needs to be truncated to 1 decimal.

Now, I'm not sure this is possible or not. But, if it is, I want to also select the amount of numbers I pull from the sample to average to be based on the sample size itself. For example, if there are 10 results in the sample size, I only want to average the lowest 3 of the 10.

Is it possible to formulate all of these rules into one cell?

View 1 Replies View Related

Logical Commands Based On Criteria?

Mar 30, 2014

View attached file. I have a complexity rating that I have developed for a set of projects that I am working on. Management of my company have signed off on this matrix as they believe it is most appropriate. In my projects pipeline tab, I have created the risk and complexity ratings using logical commands.

projects.xlsx‎

View 10 Replies View Related

Disable Commands Ribbon Excel

Jun 23, 2012

I have the code that accompanies the attached spreadsheet model that locks the copy, paste, cut, double click and so on, with the right mouse button and shortcut keys, however, the toolbar excel these functions still remain active. How well I block access to the toolbar.

Desable_Ribbon.xlsm‎

View 14 Replies View Related

Transfer Commands To Command Prompt?

Feb 20, 2014

I need to transfer two commands to command prompt. The first one is always the same and it calls a command line of another application (BEAM). The second command is for the BEAM command line. It is taken from an Excel cell. The code I managed to write is transfering only the first command, probably because after execution of the first command command prompt is getting BEAM command line. I

[Code] .....

View 2 Replies View Related

To Access Non-native Menu Commands In VBA

Jan 2, 2009

I have not been able to find any references to anywhere, so here I am....

I'm using Excel 2003 that has a menu bar added by another program that interfaces with it (via DDE, as I understand it). The DLL's from the other program have no documentation and/or ability for me to connect to the particular drop-down menu command I'm using. It is a refresh command that re-imports stock market trade data because DDE drops trade messages.

View 11 Replies View Related

Different Commands When Pasting Excel To Word

Nov 26, 2011

What are the different commands for pasting data from excel to word? The code I have below pastes excel cell data to word as a picture. What if I want to paste excel cell data as embedded in the word document (I don't want it linked, just want the user to be able to edit it as an Excel sheet within the Word document). Thinking that I need to change the "DataType:=wdPasteText" part, but I don't know the correct syntax. Tried wdPaste but that did not work.

sub mysub()
Dim WdObj As Object, fname As String

fname = "myfilename"[code]....

View 9 Replies View Related

Macro Commands When Closing Workbook

Dec 13, 2011

Is there a way (with macros, I''m assuming), to make my workbook always return to the Main tab and Save automatically --- every time somebody closes the workbook from any tab - so that the next person who opens the workbook will have it ready to go on the main tab?

View 5 Replies View Related

Copy And Paste Commands Have Changed

Mar 23, 2012

In the past when I copy and paste a cell, the new cell contains the formula from the old cell, with relationships between other cells maintained.

Recently my excel stopped copying formulas and copies only cell values from the old cell. It now acts as it used to act if I did a Copy-Paste Special -Values operation.

If I do select Paste Special, it grays out the formulas option and only lets me cut and paste values.

View 3 Replies View Related

Subroutine Stops Before Performing All Commands?

Jun 17, 2013

I have two subroutines. One subroutine updates some figures on spreadsheet 1, and then calls a subroutine that updates some figures on spreadsheet 2. Spreadsheet 2 is supposed to save and close, and return to spreadsheet 1, but it only gets as far as opening spreadsheet 2 and updating the figures. These are the two subroutines:

Code:
Sub UpdateLegalAndMeans()
Dim wBook As Workbook
Dim count As Integer ' counter for counting down backlog figures

[Code]....

View 4 Replies View Related

Sumproduct And SumIF Commands Merged

Dec 19, 2013

I am trying to extract data all countries excluding Spain and Portugal and also exclude data for certain product classes (for example excluding Products X, Y and Z)

I have written the formula below which picks up data for certain criteria I have concatenated in tab "raw data" in column A:

=SUMPRODUCT(SUMIF('Raw Data'!$A:$A,B7&D3,'Raw Data'!$W:$W)=('Raw Data'!I:I"Spain")*('Raw Data'!I:I"Portugal"))

Notes: Cells B7 and D3 are used match criteria in Raw Data A:A
Column W is the data that I need to extract i.e the numbers
Raw data I:I is a list of all countries

However this does not exclude Products X, Y and Z, only excludes countries Spain and Portugal.

I wondered if there was an easier way to create a formula?

View 4 Replies View Related

Adding Commands To Right Mouse Clicks

Feb 28, 2007

How do you add commands to a right mouse click?

I click on a cell, do a right mouse click and would like to be able to select a command that puts the current date and/or time in the selected cell.

View 9 Replies View Related

Multiple Vba Commands On A Single Line

May 19, 2008

Is it possible to have multiple VBA commands on a single line with a separator ( ; or , ) of some kind?

I am trying to keep my code as short as possible (from a scrolling perspective) and would like to have something like the following:

Dim MyArray(1 to 20, 1 to 3)
MyArray(1,1) = "Sheet1" ; MyArray(1,2) = "D8" ; MyArray(1,3) = "D9"
MyArray(2,1) = "Sheet1" ; MyArray(2,2) = "C49" ; MyArray(2,3) = "w3"
MyArray(3,1) = "Sheet1" ; MyArray(3,2) = "A23" ; MyArray(3,3) = "AB12"
MyArray(4,1) = "Sheet1" ; MyArray(4,2) = "Z19" ; MyArray(4,3) = "W12"
MyArray(5,1) = "Sheet1" ; MyArray(5,2) = "Q32" ; MyArray(5,3) = "Q23"

View 9 Replies View Related

IF-formula- IF Function Only Manage 7 Commands

Jun 16, 2009

I would like to make an IF-formula in excel. The problem is that i would like to to it for 12 different commands. i.e "IF(A1=14;243;"") but for 12 commands I saw that the in-built IF function in excel only manage 7 commands, is this true?
And how do I write the formula,

View 9 Replies View Related

Running Userforms , But Don't Know The Commands To Make Them Run

Feb 2, 2007

I've created userforms, but don't know the commands to make them run.

View 4 Replies View Related

Formatting Keyboard Commands (negatives In Red With Parenthesis)

Dec 15, 2009

I use keyboard commands whenever possible. They are much quicker than the mouse.

For years I have been using [Ctrl]+[Shift]+[1] to format cells as numeric with two decimal places, 1000 separator(,) and showing negative numbers in black with a minus sign in front.

I like to see negatives in red with parenthesis.

View 5 Replies View Related

Recorded Macro Missing Paste Commands

Nov 13, 2008

I have been using the "record macro feature". What I want the macro to do is as follows.
1. cut all data from column C and paste it into G.
2. Use the textTocolumns feature to split a comma delimited string into 2 seperate columns.
3. user the textToColumns feature to remove any leading spaces from column G.
4. cut columns d-H and shift them left startign in column C.

I can do all of this find bu using the GUI while recording the macro but when I check the vb script it's completely missing the paste commands.

When I try and run the macro it, quite rightly, returns "run-time error '1004':No data was selected to parse.

Below is the resulting recorded macro.

How do I amend this macro do include pasting the cut data?

View 2 Replies View Related

Need To Restrict Excel Shortcut Keys Commands To Run

May 2, 2013

I'm working on a VBA program prepared for accounting purpose. In that program i hide the excel ribbon by custom UI and also need to restrict excel's shortcut keys commands to run.

Can i do this with some VBA code.

View 2 Replies View Related

Issuing Word Commands From Excel Macro

Nov 20, 2006

I am trying to build a word document from an excel macro (with some success) and I am running into some issues with excel not understanding some of the word macro commands. An excellent example of this is when I am trying to insert a page break as seen in the below code snippet:

'open word and create a new document
Dim wdApp As Object
Set wdApp = CreateObject("Word.application")
wdApp.documents.Add

'put some text into the document
wdApp.Selection.typetext Text:="Document title"
wdApp.Selection.TypeParagraph
'insert a page break
wdApp.Selection.InsertBreak Type:=wdPageBreak

I know the insert break command works fine because I can get it to work within a word macro. When trying to get excel to run it in word is another story. I get the following error message:

run-time error '9118':
Parameter value was out of acctable range

Debug
wdPageBreak = empty

View 9 Replies View Related

Cell Colour Pallete Codes For Commands

Oct 18, 2008

where I can find the colour pallette codes for commands like, as my suck it see method of guessing the right colour is pretty time cosuming.
selection.Interior.ColorIndex = 34

and secondly, can rgb be used to fill cells colour i.e. something like
range.colour or background whatever the command is. rgb(255,255,255)

View 9 Replies View Related

ADVERTISEMENT