Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Deleting VBA Modules And ActiveX Controls


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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
VBA Commands For ActiveX Controls
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


View Replies!   View Related
Conditional Disabling Of ActiveX Controls
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 Replies!   View Related
Running Macros And Activex Controls
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 Replies!   View Related
ActiveX Controls On Worksheet - Fmbackstyle
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 Replies!   View Related
ActiveX Sheet Controls Basics
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 Replies!   View Related
Loop Through ActiveX Controls In Worksheet
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 Replies!   View Related
Programmatically Create ActiveX Worksheet Controls
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 Replies!   View Related
Modules In VBA
Can i know how many modules can be used in a VBA Program

View Replies!   View Related
Ignoring 1 Of Two Modules
I have managed to get it almost done with lots of help from here the last issue I have is this:

The module I want transferred is working the assigning of the macro within that module to the button is working, however, when it gets to the FillSalesSheet section it opens the said workbook but then jumps to the module and the FillSalesSheet in that.

Module 1 which I would like ignored on the newly formed workbooks looks like this: ...

View Replies!   View Related
Removing Modules
It seems like I am asking a question at least once a day. I searched the net and forums and could not find an answer to this question. Thanks to everyone for the help I am making a lot of progress because all of you. Basically, I have a read-only workbook which the user gets a prompt to save as a new workbook for editing purposes. I have a autorun macro so now when the user opens their workbook, it contains my autorun macro. I do not want this. Is there a way to remove modules so that the users copy has no macro information?

View Replies!   View Related
On Open Run Two Modules
I have two modules I wish to run when I open a workbook. The reason I have them as modules is they are required to be run manually, also.

Is it possible for me to set a Workbook_Open() sub that will run these two modules?

The modules names are Module1 and Module2.
The modules stipulate what sheets etc they have to act on, I only need the modules to be initiated.

View Replies!   View Related
Class Modules?
I'm wondering about class modules and what they can be used for etc etc? i want to continue developing my VBA and feel that this area is the next step?

View Replies!   View Related
Export All Modules
I had a search and look around, but couldn't find any information on how to quickly (automatedly) export all the modules in a workbook to text files.

View Replies!   View Related
Web Queries In Modules
I'm trying to produce a spreadsheet that pulls infomation from different web pages throughout the day. It needs to pull quite a large amount of information from different web pages hence I wanted to produce a subroutine which could be called with different variables for each web page, i.e. url, field, destination, etc.

To actually produce the query I recorded a macro and then used the code it produced. I then changed the URL to url, a string variable which holds the urls of the pages. The destion address was changed to destination, a range variable to store the destionation location, and the webTable was changed to table also a string variable to store the table number. This is shown below.

With ActiveSheet.QueryTables.Add(Connection:= _
url, Destination:= destination)
.Name = "q?s=%5EFTSE&m=L_137"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True.............................

View Replies!   View Related
Delete All Modules
I was looking for code that actually deletes ALL modules in the VBA code after I terminate a particular program.

View Replies!   View Related
Pass Variable Between Modules
I created the following sub to signal when a macro in Module 1 is complete:


Public Sub Done()
Dim complete As Boolean
complete = True
End Sub
I placed this just before the end sub in the macro for which I am trying to detect that it has finished executing:


Call Done

End Sub
In the Sheet 1 Module, the code fails at the statement:


If complete = True Then
The error returned is "Variable not defined." All three subs are declared as Public. Why does the Sheet1 sub not recognize the variable "complete" from the Module 1 macro?

View Replies!   View Related
Vba: Difference Between Sheet And Modules
If I put:

Sub t1()
Worksheets("sheet1").Cells(1, 1).Value = "abc"
End Sub

on sheet1, it works. But if I put it on module1, then it does not work. What do I miss here?

View Replies!   View Related
Delete Multiple Modules
I have successfully rewritten all code in one workbook project. I have cut my Standard modules from 17 to just 4 modules.

Is there a way to remove all 17 modules at once so I can replace them with the 4 new ones?

I know that right clicking on the module and then click on "Remove...." will do it, but I would have to do that 17 times.

Not too bad if this was just one workbook to do. I have about 10 other books to do the same thing to. So that would be doing the right click 170 times.

I'm not even sure if this is possible. And if it is, I was thinking at probably having the code in a workbook open event to delete the Standard Modules??? Then I could just import the new modules.

View Replies!   View Related
Range As Variable For Use Between Modules
I want to set a variable for the range I want to copy so that the range can change between modules Sometimes I want to copy a1:x1 and Sometimes I want to copy a1:m1 what I did but it did not work

Dim myrange As range
myrange = a1:x1
range myrange.select
Selection.Copy

View Replies!   View Related
Protect VBA Modules & Code
I would like to protect the VBA modules I have written in a spreadsheet. Is there any way of password protecting these (as there is with Excel workbooks/worksheets) -such that users can not see the VBA code unless they enter a password?

View Replies!   View Related
How To Create VBA To Delete Previous VBA Modules
I would like to create some VBA that looks for sub's or modules and delete them.

View Replies!   View Related
Joining Modules With User Forms
I have the following three codes repeated in five different User Forms,

Is there a way to put them all in one independent module?

View Replies!   View Related
Export All Modules In Current Project
I'm trying to write some code to export all the standard modules (not class or worksheet modules) in my project to a directory on the network. I'm looking for something like below, but can't quite find the syntax to get the modules collection.

I know this syntax is not correct, but I'm sure it has to be something kind of similar to this.

Dim MyModules As collection
Dim MyModule As module

Set MyModules = currentproject.modules
For Each MyModule In MyModules
MyModule.export "x:directory1Directory2File.bas"
Next MyModule

View Replies!   View Related
Delete Modules From Multiple Files
I have several organized folders which contain contain, in total, hundreads of files all created from the same master file. The Master file contains several macros used to sort and organize data copied from our engineering database (web-based).

After a file is created the macro is no longer needed. I am looking for some direction on how to go about opening and saving the files which are located on a network drive.

I have found some good starting point here for the actual removal, and I am working on a new master file which doesn't keep the macro when saved (save as...).

View Replies!   View Related
Pass Array Variables Between Modules
I am trying to pass a public variable to another module in the same workbook. On Module1 I declare at the top

Public stores
Sub Main()
Dim stores(1 To 100, 1 To 10, 1 To 10)

Reader

...which then calls the procedure Reader in Module2

Sub Reader()
let x=1
let y=1
let z=1
let stores (x,y,z)=activecell.value

I've left out the portions of code that seem irrelevant. When the macro runs, I get a type mismatch error on the "let stores" line. If I move the code from Reader into the procedure Main, it works, so it seems to be an issue with passing the variable. I haven't used multiple modules very often so this is probably a very basic issue.

View Replies!   View Related
Import Modules & UserForms From Workbook
I am basically after a script that can be run from a persons workbook via an Excel Addin, e.g. they click an "Update" button, and the script imports the Forms and Modules from another workbook that i specify.

I do not however want the data held on the spreadsheet to change, just the Modules & Forms in the VBA project.

View Replies!   View Related
UserForm Variables In Public Modules
I make a userform called Option and now i want to call some of the variables of the userform in a module. First i try to copy this variables in a cell with the next

ActiveSheet.Cells(2, 3) = Me.MediaP.Value

and it works. But now, i want to call to MediaP in a module. For do that I try to do this:

With Worksheets("Hoja2")
.Range("B1").Value = Option.MediaP.Value

but it doesn't work. I don't want to use public variables or copy/use the information of the cell(2,3), i only want to know how use the information on the textbox called MediaP in a module.

View Replies!   View Related
Automatically Copy Modules To Other Files
I need to copy a code which I have in Module 1 of File 1 to numerous other files. Is there any quick way of doing that?

View Replies!   View Related
Change Names Of Modules. Module1 Etc
Is there a way to change modules' names iso module1 give a real name?

View Replies!   View Related
ActiveX Collections Via VBA
I have a workbook which is a reasonably standard financial model (PL, BS, CF, etc). I have a control worksheet with a number of AcitveX controls (Command Buttons, Toggle Buttons and Combo Boxes) that drive the rest of the model (e.g., depreciation methods, trade terms, etc).

When the workbook opens I want to fill the lists of the various ComboBoxes, but I can't work out how to access JUST THE COMBOBOXES as a defined collection (e.g., MyWkSht.ComboBoxes[Index] or some such), or as a sub-type of a meta-collection (e.g., MyWkSht.Controls with Type="ComboBox"), without referring to them directly by name (e.g., MyWkSht.MyComboBox).

The ComboBoxes are NOT on a UserForm, but are embedded on the sheet as OLE objects.

I really don't want to have to hard-code the combobox names in directly, because I may need to add/remove them as the model develops - and anyway it's just WAY inelegant.

View Replies!   View Related
SHOW ALL CODE MODULES & SUBS IN A WORKBOOK
A recent message prompted me to complete a project to show all modules and subroutines in a workbook.

As use of VBA to manipulate the VB Editor is a recurring issue I thought it a good idea to share this.

Option Base 1
Dim WBname As String
Dim ws As Worksheet
Dim TitleStr As Variant
Dim VBProject As Object
Dim ToRow As Long
Dim ToCol As Integer
Dim ComponentType
Dim MyComponent As Object
Dim ComponentName As String
Dim TypeArray As Variant
Dim StdCol As Integer
Dim LastLine As Long
Dim CurrentLineNumber As Long
Dim CurrentLineText As String
Sub SHOW_ALL_MODULES()
WBname = ActiveWorkbook.FullName
On Error Resume Next
Set ws = ActiveWorkbook.Worksheets("WB Contents")
If Err.Number 0 Then ' sheet not exist....................

View Replies!   View Related
Use Activex Combobox In The Worksheet
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.

View Replies!   View Related
ActiveX Combobox Code-run
I've created a workbook on whose main sheet there are three activex comboboxes. The first one contains a list of years, and the second one contains a list of months. The user selects the year, and it goes to its linked cell. The user then selects a month, and using the Combobox_Click event, should run a sequence of 7 macros (i.e. using the "call updateinformation1" macro, which is the first of the seven updateinformation macros).

The sequence of macros ran fine before I added the comboboxes. After adding the comboboxes, the final (7th) macro somehow goes back to the first macro and then causes a "ClearContents method of Range class failed" error on the "Selection.ClearContents" line. These are the first and seventh macros:

Sub updateinformation1()
'clears previous information to make way for new information
Application.ScreenUpdating = False
Application.EnableEvents = False

Worksheets("Criteria2").Activate
Cells.Select
Selection.ClearContents

I can't for the life of me figure out why it's looping back to the first macro instead of just stopping after the seventh. I have other workbooks that do something similar, and I don't have this problem. Am I missing something?

if I need to post all 7 macros?

View Replies!   View Related
Refreshing Activex Textbox
I've built a spreadsheet with quasi-animation for a physics class on projectile motion. In it there's a animated chart showing velocity change as a function of distance. The chart has on it an ActiveX textbox that refers to a cell that contains a formula showing "V_y = {velocity}". I've tried a number of things to get the display of the textbox to change during the animation. If I break the code at the line that updates the cell referenced by the textbox the display changes. But I can't figure out how to get the display to update while the code is running.

View Replies!   View Related
Unwanted ActiveX Warning
I have used the OWC.Spreadsheet 11 object in a form. But since then, I get a warning, usually when I open the spreadsheet, saying "This application is about to initialize an ActiveX Control that might be unsafe. If you trust the source of this file Select OK.etc,etc". I am using Excel 2003. Is there anyway of getting rid of this message? I am worried that users will see it, worry about using it and press cancel instead of OK at the prompt, thus rendering my form useless.

View Replies!   View Related
How Can We Rename The Modules, With Out Exporting, Remaming It, Then Importing It Back?
When you create or record a maco Excle creates Module(s), Module1, Module2, Module3, so forth and so on.

How can we rename the modules, with out exporting, remaming it, then importing it back?

View Replies!   View Related
ActiveX Control Can't Create Object
I copied a routine from this forum that would email a single sheet from a 10-sheet workbook to a recipient. This worked great for a long time, then all of a sudden I started getting the "ActiveX control can't create object" error. Here is my

View Replies!   View Related
Activex Combo Box Format Text
How do I format the text in an ActiveX combo box? I would like to increase the font.

View Replies!   View Related
ActiveX Command Button Text Looks Bad
I have created an ActiveX command button and the text on the button looks crude. I know that is not very descriptive but I am unsure how to describe it. The text looks jagged and some letters are missing chunks of their letters. The font I have chosen is Calibri.

If I create a Form Controls button I do not have this problem but I want the button to have a particular background color so I do not believe I can use a Form Controls button.

I do not know why the other Calibri text on the worksheet looks fine while the ActiveX Calibri text does not. The attached image shows the two buttons with the top one being the ActiveX button while the bottom is a Forms button.

View Replies!   View Related
Transparent Background Of ActiveX Control
I have an ActiveX Label control with a transparent background. When I click on the label the background turns white and stays that way as long as the mouse is over the label, obscuring whatever's underneath. (It also very slightly alters the character spacing.) What object/property controls this? How do I adjust it?

While I'm here, is there an easy programmatic way to assign arbitrary RGB colors to ActiveX controls? The palettes available from the Properties window are fairly limited. A google search for "ActiveX colors" yields a deluge of marketed products, so it's hard to find useful information.

View Replies!   View Related
Can't Change Combobox Properties (activex)
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 rValsIf rCell.Validation.Type = 3 ThenlTop = rCell.ToplLeft = rCell.LeftlHeight = rCell.Rows.HeightlWidth = rCell.Columns.WidthWith 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 WithlCount = lCount + 1End IfNext rCellEnd Sub

View Replies!   View Related
Reference ActiveX ComboBox Value On Worksheet
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.

View Replies!   View Related
ActiveX Comboboxes Change To Icon
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 Replies!   View Related
Add Downloaded ActiveX Control To Toolbox
Version: excel 2003
OS: Win XP

Currently using VBA do develop a tool for Excel. I've downloaded a .ocx control and am wondering how one would go about adding it to the toolbox?

View Replies!   View Related
Class Modules: Provide Users With In-Code List Of Constants
I am working with a class module and some fields require specific inputs that another coder may not intuitively know. How can I create a list of acceptable conastants that will appear as a drop down when the user is coding with the custom object?

For example:
I am creating an Email Engine object that will handle all my emailing needs in code, because coding emails is such a pain. Then I can just create the blasted thing, import the class module where needed and never have to worry about it again. Some coworkers are interested in using the module for their projects when I am finished. I want to put an option for message flags in the object, but only certain colored flags are allowed. How can I get the VBE to show a little drop down with the acceptable flag constants when another user types

[Object Variable].MessageFlagColor = {drop down list of options}
I started with

Public Property Let MessageFlagColor(Value As Constants)

End Property
But that hasn't gotten me anywhere.

View Replies!   View Related
Copy And Paste To Worksheet Using Combobox (ActiveX)
I managed to put the names of my other worksheets into some comboboxes in the main worksheet by using this ....

View Replies!   View Related
ActiveX Click Control--STOPS When On Another Sheet
RE: Macro ActiveX Click Control--It stops when on another sheet

Macro runs OK when run from menu.

An activeX click button was put on sheet B.

Right click the control, View Code, Paste in code from macro, then click button.

It runs the part of the macro that applies to sheet B, goes to previous sheet A, and then stops. The range select line for the named range on sheet A is highlighted in yellow. It stops when selecting any range or cell on sheet A, like B2.

Any idea why this code does not work on sheet A when ActiveX button is on sheet B? Is there someplace else that the control can go? Using Excel 2000.

See part of code below, which starts on sheet B:

' Macro recorded 11/22/2007

Range("SortAll").Select
Selection.Sort Key1:=Range("T2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Previous.Select
Range("FormBase").Select (STOPS HERE)
Selection.Copy
Range("Formul").Select
ActiveSheet.Paste
ActiveSheet.Next.Select
FormBase is a named dynamic range
FormBase=OFFSET(A!$A$2,0,0,1,MATCH(1E+306,A!$2:$2))

View Replies!   View Related
Text Inconsistencies In ActiveX Combo Boxes
I'm building an application that uses a large number of activex combo boxes. These boxes are getting loaded from specific fields in an associated database.

I know ActiveX combo boxes can be buggy; I'm wondering if anyone has found a way around the text appearance inconsistencies seen in these controls? e.g., sometimes the text is smaller, sometimes it's spaced wider, sometimes it appears at the top of the combo box sometimes in the center.

I put a screen shot of what I'm talking about in a word doc along with a control properties screen shot, All controls have the same properties except for the text property.

View Replies!   View Related
OleObject ActiveX ComboBox Event Procedures
I've searched the web and OzGrid all day, and still have this question: what is the equivalent for .OnAction when using a ComboBox as shown in the code below. Everything else works as desired.

Loopcntr = 1

Set xyz = ActiveSheet. OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, DisplayAsIcon:=False, _
Left:=Target.Left, Top:=Target.Top, Width:=Target.Width, Height:=Target.Height)

'''??? xyz.OnAction = "TDListing"
xyz.Object.BackColor = RGB(204, 255, 204)

Do While Loopcntr <= TaskListArraySizeHolder
DDholdName = TaskListArray(Loopcntr)
xyz.Object.AddItem DDholdName
Loopcntr = Loopcntr + 1
Loop

View Replies!   View Related
Customize ActiveX SpinButton Value - Not Decrementing (or Incrementing) Correctly
I have an Active X SpinButton control directly on a spreadsheet that changes the input cell. (The idea is that later on, there'll be a bunch of these input cells, accompanied by their respective SpinButton controls).

I'm allowing the user to define the Min, Max, and Step (SmallChange) value of the SpinButton because (1) I need it to do decimal points and negative numbers and (2) that's what my boss would have wanted.

So I set the Min, Max, SmallChange value in the SpinButton properties to "0" then try to manipulate them in the VBA code.

What went wrong with the code is hard to explain. But basically, when I use the SpinButton to increment or decrement, it didn't quite do it correctly. If I fix the increment part of code, then the decrement part of the code goes wrong and vice versa.

An example: I have Min as "0.5%" in cell B7; Max as "4.5%" in cell B8; and Step (SmallChange) as "0.5%" in cell B9. The Input cell is in cell B10 where user can either type in a value within the defined range (enforced by data validation) or use the SpinButton to change the value.

To replicate the problem: I type in "1.0" in the Input cell and click on the left SpinButton and it would not allow me to go to the defined lower limit (which is 0.5%) in this case.

Here are my codes:

Private Sub SpinButton1_SpinUp()

Dim MyMax As Variant
Dim MyStep As Variant
Dim MyInput As Variant

MyMax = SpinButton1.Max + Range("B8").Value
MyStep = SpinButton1.SmallChange + Range("B9").Value

MyInput = Range("B10").Value...................................

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved