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


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
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
Modules In VBA
Can i know how many modules can be used in a VBA Program

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
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
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
Adding Variables To VBA Controls
I have a userform with a large number of textboxes. Say 100. I want to clear them all after the user submits the data to the spreadsheet. Essentially resetting the userform.

Currently I have:

userform1.textbox1.value = ""
userform1.textbox2.value = ""
userform1.textbox100.value = ""

Is there a way to change the textbox number to a variable? Something like:

For textboxN = 1 to textboxN =100 Do
textboxN.value = ""

View Replies!   View Related
Skinning VBA Userforms And Controls
I'm trying to skin an Excel VBA userform and all the controls on the form. I found a very good example that skins a userform via a class module. It doesn't however skin command buttons, text boxes etc. I imagine the techniques would be similar.

View Replies!   View Related
Vba Error Run-time 429 Activex Component Can't Create Object - Excel & Outlook 2007
I have some VBA code that in one portion of it an Outlook e-mail is created and the active workbook is attached and sent. This code used to work on Excel & Outlook 2003, but now in Excel & Outlook 2007 I'm getting the error:

Run-time error '429':
ActiveX component can't create object

and is stopping on the line

Set OutApp = GetObject(, "Outlook.Application")

FY If I comment out the Set OutApp... and If OutApp.... lines and uncomment the Set OutApp create object line, I still get the same error.

Dim OutApp As Object
Dim OutMail As Object
Dim SigString As String
Dim Signature As String

'Set OutApp = CreateObject("Outlook.Application")
Set OutApp = GetObject(, "Outlook.Application")
If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

View Replies!   View Related
Assessment Questionnaire With Controls & VBA
I am designing a skill assessment questionnaire and want to use radiobuttons to keep it user friendly. I have about 100 line items with 6 radiobuttons and 1 checkbox. I want to assign a linked cell to each button and group the radiobuttons of each row (plus set a default value.) As the number of buttons is large I am looking for a way to do this efficiently without manually changing the GroupName in the properties window. Currently, I create the buttons automatically but cannot change the desired attributes (groupname and linkedCell) unless I explicitly address the respective button by its name directly.

ActiveSheet. OLEObjects("OptionButton2").Object.GroupName = MyRange
ActiveSheet.OLEObjects("OptionButton2").LinkedCell = LinkOB6

This it not practical though due to the large number of buttons in the spreadsheet.
Currently I try to address the buttons with other variables but this does not work. my current code is the following (for only 5 rows and 2 buttons per row):

Sub Buildbuttons()
Dim RowNo As Integer
Dim i As Integer
Dim x, y As Integer
Dim xdif As Integer
Dim ydif As Integer
Dim LinkOB1, LinkOB2, LinkOB3, LinkOB4, LinkOB5, LinkOB6, LinkCB1 As String
Dim OB1, OB2, OB3, OB4, OB5, OB6, CB1 As String
Dim MyRange As String
' now of rows to fill
RowNo = 5
'define starting position and distances
x = 20
y = 5
xdif = 54
ydif = 18
i = 1.................

View Replies!   View Related
Deleting A Row Using VBA
I am in need of code that will match a variable, "TheName", to the values in a range named "Phases". If a match is made then I want to delete the entire row which the value is matched in.



My variable, "TheName" = Cat

I want the code search only the range "Phases" and delete the entire row in which the variable "TheName" is found. so in this case it would delete the entire row in which the value of "cat" was matched.

View Replies!   View Related
Deleting All VBA Upon Closure
This thread is somewhat of a cross post, the cross post is here
The cross post relates to a MS-Word related question.

However, I do have the same question, just for MS-Excel.

Right now my Macro runs when my Excel application opens.

But when my users are Xing(closing) out of the Excel application is there a way to remove all VBA upon closure of the Excel sheet.

View Replies!   View Related
Deleting The First 8 Characters In Vba
I have a column that has all the digits in the phone number. What I would like to do is remove the first 8 characters example 111-222-3333. I want to remove all the ones dash twos and dash. Can someone help me please ?

View Replies!   View Related
VBA And Deleting Rows
I have an excel spreadsheet that contains about 1000 rows and about 25 columns. The file contains employee information, name, id, cost center, department, title, FTE...etc. Column E contains the cost center which is a 7 digit number (i.e. 8001234). Within the 1000 rows of data there somewhere to 70 cost centers. I would like to delete all rows where a cost center does not match a list of 13 cost centers. I'm thinking I need some kind of array where I type in the 13 cost centers in the code, but I'm unsure of the syntax within VBA.

Also, the second step of the code I'm looking for is to delete all columns except for Column B, E, J and L. Those, by the way are Name, Cost Center, Job Title and FTE.

View Replies!   View Related
VBA :: Deleting Blank Row
Public Sub Delete_Blank_PO()
Dim iLastRow As Long
Dim Rng As Range
Dim r As Long
Dim x&
For x = Cells(Rows.Count, 2).End(xlUp).Row To 6 Step -1 '***set coumn 6 as range
With Cells(x, 6)
Select Case "" '.Value

View Replies!   View Related
Deleting Rows With Certain Values In VBA
I'm running into an issue when I try to generate a report for the largest five values in a report. Some of the cells have a zero divisor in their formula so they come up with the #DIV/0 error. I've put in an If statement to make those equal 0, but the LARGE formula still returns #NAME?. Any ideas on how to get the LARGE formula to work.

I would like to be able to delete the rows with zero in the reference column, so their data is not even in the report. Does anyone know how to do this in VBA?

View Replies!   View Related
Deleting The Entire Row In VBA
I have a spreadsheet with thounsands of rows. Some of the rows I'd like to delete because thier values are below 5%. I wrote a VBA code, which primary function is to look for a cell and delete the row if the value is less than or equal to 5%. The code looks fine but I get an "Infinite Loop" anytime the first row value is not true. Below is the code,


Sub Delete_Zero_Factors()

Dim i As Integer
Dim CUSIP As String


i = 0

While Not (IsEmpty(ActiveCell.Offset(i, 0).Value))
CUSIP = ActiveCell.Offset(i, 0).Value

Do While (ActiveCell.Offset(i, 1).Value

View Replies!   View Related
VBA - Deleting Rows And Move Up
I need to look into a particular cell on a specific column.

if this cell does not contain a number, then I need to delete the entire row, not column.

and then of course do this for 30K+ rows.

if sheets("sheet1").cells(x,7) is not a number ( dont know how to write this )

then delete row and move up.

View Replies!   View Related
Deleting Range Of Cells Using VBA
I need to have my macro select a certain range of cells with a specific product code and delete them. What would be the code for it (product code 107). Here is what i have so far:

Workbooks.Open Filename:=fNameAndPath, UpdateLinks:=1
ActiveWorkbook.SaveAs Filename:=myStrPath1 & myStrPath2 & myFile3 & myExt, FileFormat:=xlNormal, CreateBackup:=False
Cells.Select.Range ("A78:K153")
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveWorkbook.SaveAs Filename:=myStrPath1 & myStrPath4 & myFile4 & myExt2, FileFormat:=xlCSV, CreateBackup:=False

View Replies!   View Related
Deleting A Workbook From A Userform Vba
How is it possible to delete a workbook from a userform in an open workbook?

View Replies!   View Related
Deleting BuiltIn Command Bars VBA
I have the code below. But When I run it, the line marked * throws up an error.
I do have custom built toolbars in my spreadsheet. It should delete them all.
I am using Excel 2003.

Sub DelCommandBarsCustom()

'Del all command bars of specific criteria

For Each objCommandBar In Application.CommandBars
If objCommandBar.BuiltIn Then
* objCommandBar.Delete
End If
Next objCommandBar

End Sub

View Replies!   View Related
VBA - Deleting Rows With Blank Values
I am trying to write a macro which will delete all rows where cells in column B are blank. I am new to macros and since I have only been able to record them, I am having a tough time hand-writing this one.

View Replies!   View Related
VBA Deleting Data Until Empty Cell
I have searched around and cannot find exactly what im looking for. I have a table of data and want to delete it all by clicking a button. I only want it to delete the rows which have data in.

A1 = Apple
A2 = Apple1
A3 = Apple2
A4 = Apple3
A5 = *Empty*

So it would delete A1-A4 and not delete A5.

View Replies!   View Related
Filtering And Deleting Rows With VBA Code
I've got a row of data with a column showing some sales including amounts with 0. Any row with 0 I want gone, so I want the macro to filter the list, delete all the rows with 0 in that column, and then display what's left, all the rows with some number other than 0 in that column.

The code below should filter the 0 rows, delete those rows, and then display what's left. I don't want the heading row deleted, but everything below it that is filtered. What my code below is doing now is not deleting everything it should. How can I fix it.

Sub prepmetrics()
' prepmetrics Macro
' for use with monthly report - don't forget the new coversheet
Application.ScreenUpdating = False

Selection.AutoFilter Field:=14, Criteria1:="0"

' this is the challenge here. How do I tell Excel to select, relatively, whatever
' rows are now showing as a result of the filter?

Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

' there may be a number of different criteria in different columns, so
' we rinse and repeat as many times as necessary but, again, each filter/delete
' task should delete all rows filtered

Selection.AutoFilter Field:=16, Criteria1:=">7500"
Selection.Delete Shift:=xlUp

Application.ScreenUpdating = True

End Sub

View Replies!   View Related
Getting #VALUE Error When Deleting Blank Rows With VBA Code
I have this code that I have had for a while.. it works okay on my computer and does as intended... it deleted all empty rows in the selected range after checking to see if any cells contain anything that makes it look blank but isn't (it cleans those cells).

So on my computer it works... on a coworkers computer it converts every used cell in the usedrange to #VALUE...

View Replies!   View Related
VBA Code For Deleting Columns With 0 Value In Specific Cells
I am trying to write a VBA code in excel to delete any columns when a cell in that particular column = 0 (zero). That cell would start at B19 and subsequently follow to C19, D19 and so on.

View Replies!   View Related
Security Prompt Even After Deleting Macros Using VBA Code
I have used the following Macro that deletes the Macro code. It then saves the file and quits excel.

Sub removeAllCode()
Dim awi
Dim awcl As Integer
Dim count As Integer
Dim i As Integer 'loop counter
On Error Resume Next
count = ActiveWorkbook.VBProject.VBComponents.count
For i = 1 To count
Set awi = ActiveWorkbook.VBProject.VBComponents.Item(i)
awcl = awi.CodeModule.CountOfLines
awi.CodeModule.DeleteLines 1, awcl
Next i
Set awi = Nothing
End Sub

However when I open the Excel File, I still get the Macro Prompt. I have checked in Macro list as well as in VB Editor and no Macro code exists

If I have the file manually (or do a save as) and then reopen the file, I no longer get the Macro prompt.

I have tried various examples listed in different forums/sites but nothing works.

View Replies!   View Related
VBA Code For Deleting Rows Where Cell In Column A States
I'm trying to get a VBA macro put together that would delete all the rows, throughout every sheet, and shift up wherever the cell in column A = "Delete Row" in a workbook I've made. In the below copy of the workbook you can see that a lot of the information from all the tables is linked to the topmost table on the 'Mix Detail Sheet'.

If the the Cell in column A is blank on that first table, its corresponding cells in tables throughout the workbook displays the words "Delete Row". I need the macro to delete the rows and shift everything up where this occurs.

Ideally I'd also like the macro to then delete the rows in that first table where the column A cells are blank.

View Replies!   View Related
Deleting Multiple Worksheets Which Ends With The Word Data Through Vba Code
I have many worksheets which ends with the word " data". Is there a way to delete all the data sheets at one go through VBA code.

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

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


...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
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
Copyright 2005-08, All rights reserved