Vba Delete Of Oleobject Resets Globals?

Jun 6, 2007

I'm developing a series of schedules that organize building plans. In one schedule I maintain a list of areas (called spaces, like a lobby or a conference room). I programatically maintain the list of spaces in the schedule for sorting and other external functions that are irrelevant. In the process I occasionally delete a worksheet. My worksheets have 4 buttons and a combobox. Whenever I delete a worksheet with these objects, I lose ALL of my module-level variables. These include two collections, a boolean, and an integer.

I'll post only the code for now. If you want to replicate, remember to place at least one button or combobox on your worksheet. You'll need to execute each sub manually. I have placed debug statements to print the globals to the immediate window. Notice that the 3rd debug statement shows the globals as present when it's called from the delete routine; however, if you manually execute that print routine after the delete, the globals are gone!

Option Explicit
Const SHEET_TARGET_RANGE As String = "C13:K36"
Dim m_objLocal As New Collection
Dim m_objMaster As New Collection
Dim m_bln As Boolean
Dim m_int As Integer
Public Sub Debug_Populate_Globals()
Dim intLoopCounter As Integer
Set m_objLocal = New Collection
Set m_objMaster = New Collection
For intLoopCounter = 1 To 8
m_objLocal.Add intLoopCounter, CStr(intLoopCounter)
m_objMaster.Add intLoopCounter, CStr(intLoopCounter)....................

View 6 Replies


ADVERTISEMENT

OLEobject VBA

Jul 25, 2008

I have a VBA question. So I have a radio command control in my excel spreadsheet that is linked to a particular cell. What VBA code would I enter into that control that would make the control visible = false based on whether or not the cell to the right of linked cell meets a certain criteria (= 1 for example.)

View 9 Replies View Related

Create Sequential List That Resets To 1 When Value In Another Column Changes?

Sep 18, 2013

EQJq7ZC.jpg

I have three columns of data (A,B,C) and I want to add a formula or script to create the data in column D. The 'XXX's are all numerical values and will vary.

Column D should be a sequential list, which restarts at 1 any time that either:
- the value in column A changes, OR
- column A stays the same, but the value in column B goes from something to nothing, together with the value in column C going from nothing to something (or vice versa).

View 1 Replies View Related

Macro Resets Total In Text Box If A Number Deleted?

Jun 26, 2014

Attached file 140625 box adds.xlsm has three textboxes that are totalled in the fourth box.

The Code updates the total automatically as you ENTER numbers into cells, and overcomes "blanks", but I need it to update the total if you delete any of the three numbers.,

As shown below, I set the Code to "Exit Sub" if a cell becomes blank, but can't see how to invert it without going through the same "checking" process twice for the other cells.

As the Code structure is essentially the same for each box, I've just shown the first one here:

VB:
Private Sub TextBox1_Change()
If TextBox1.Value = "" Then Exit Sub [code]....

View 1 Replies View Related

OleObject ActiveX ComboBox Event Procedures

May 23, 2009

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

Immediately Change Back Color Of OleObject

Feb 3, 2008

When I click a Command Button I want a label to change color FIRST and THEN a message box to appear.

I use the following code, but what happens is: FIRST, the message box appears and THEN the label changes color, which is the wrong order. The label needs to change color FIRST.

Private Sub cbA_Click()
newcolor = vbWhite

ActiveSheet. OLEObjects("label1").Object.BackColor = newcolor

MsgBox "done"

End Sub

View 6 Replies View Related

Macro To Refresh Pivots Also Resets Chart Data Label Fonts

Aug 20, 2013

I have 6 pivot charts within a document. I use a macro associated with a button to refresh the tables and therefore the data displayed on the charts.

However, each time I refresh the data, the 'data label' font size also refreshes. What is particularly odd is that 5 of the charts are pie-graphs, of those 5 graphs, the font size only resets on 4 of them (with the data labels in a single chart staying at my desired font size).

I can manually put the font size back to that which I desire but I'd rather it just didn't change in the first place.

I've had a look to see if there's any noticeable differences between the 5 charts but there's nothing which I can find.

I'd be happy to run a bit of VBA to set the font size as I want it, if there isn't a better solution.

View 1 Replies View Related

Popup Calendar - Unable To Set Left Property Of OLEObject Class

Aug 29, 2012

I have popup calendar in a spread sheet that works fine until I share the film which produces the error in the title. Here is the code for the calender:

Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
'ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
End Sub

[Code] ...........

View 1 Replies View Related

Delete Multiple Columns By Column Name No Criteria Need, Just Delete Them

Jun 24, 2009

I would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.

And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth

View 4 Replies View Related

Delete & Merge Columns,Delete Rows With Filter, Etc

Jul 15, 2009

1. Remove J,K,N,A Columns,

2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy

3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value

4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only

5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )

View 3 Replies View Related

Search For Text Delete This Row And Delete The Row For Every Instance

Apr 29, 2008

I have an formula if statement that returns "deletethisrow" if the test is true.

For every occurence of "deletethisrow" I want to delete the row. The number instances will be variable each time I run the file. So maybe it will find that string, maybe it will find 10 instances. I want to do some kind of loop that won't error out when it cannot find "deletethisrow", but will delete the rows for each instance where it does find this string.

I know it was verbose, but if I just do a loop for a fixed number of loops it will error out if it runs out of rows to delete.

View 9 Replies View Related

Delete The Columns First Then Rows Will Not Delete

May 22, 2008

Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.

View 12 Replies View Related

Delete Duplicate Entries, Keep Last And Delete First

Feb 15, 2010

I found this sample code that works from top to bottom of a spreadsheet. But I need something that will delete the first entry and keep the last entry. My data is sent from one spreadsheet to a Master and sometimes the details can be sent twice, if the responsible person forgets to enter one line of production. The criteria should be the first 5 Columns of the sheet.

Sub Dupe_Killer()
Dim str As String
Dim str2 As String
Dim c As Integer
Dim i As Integer
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("SAMPLE").Select
rw = Cells(2, 1).End(xlDown).Row
'Sort Data by Date, Location & Number
Range(Cells(1, 1), Cells(1, 14)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Cells(1, 1), Order1:=xlAscending, Key2:=Cells(1, 2) _
, Order2:=xlAscending, Key3:=Cells(1, 3), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ ....................................

View 2 Replies View Related

Delete Content Only Of Every 5th Row - Do Not Want To Delete Row

May 31, 2013

I would like to delete the content only of every 5th row - I do not want to delete the row.

I have about 6000 4 line addresses in one column and between each address is the number 0. Need only to delete the 0.

View 4 Replies View Related

Macro Delete Page (delete A Sheet In A Macro But When I Run The Macro)

Jun 19, 2009

I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.

Sheets("Tel").Select
ActiveWindow.SelectedSheets.Delete

View 4 Replies View Related

Add/delete A Row

Nov 18, 2009

I have sheet 1 containing S.NO, Name and amt. sheet 2 - will display S.No from sheet 1 automatically..but the probelm is if i try to insert a new row (in sheet 1) in between its not displaying in sheet 2. Similarly when i delete a row in sheet 1 its showing as # ref in sheet 2. How to correct these. Do i need to write any macros.

View 2 Replies View Related

Delete 2nd Last Row

Jan 2, 2012

I need a code that will find "Grand Total" in column H and then select the row that is 2 above it and delete so if "Grand Total is in H61 it will delete row 59. the cell references can change depending on the report. is this possible?

View 4 Replies View Related

Delete Row Contains No Value

Dec 6, 2012

S.N.
NAME OF ITEMS
MRP
PACKING SIZE
DISCOUNT (%)
VAT RATES (%)
ORDER QTY
IN
PCS
ORDER QTY
IN
BOXES
NET PRICE
FOR
PCS
TOTAL
PRICE
COPY
CODE

1
Aamlki Rasayan - 100 grm.
65
200
20.63
5
150

[Code] ........

I want to delete rows contains no value. Eg. Column H is empty. I want to delete entire column not contain value. Or column G some rows not contain value, how to delete with macro.

View 9 Replies View Related

Delete The Value In The Same Row

Mar 1, 2008

I want to delete the item in the row by red on condition If there are cell.value= C and cell.offset(0,-1).value= 33, then place all the cells in rows below in place of deleted cells (red).

Row A B C D E F
--------------------------
6 - 11 a 1 1 1 1
7 - 22 b 2 2 2 2
8 - 33 c 3 3 3 3
9 - 44 d 4 4 4 4

------------------------

The result of the Row A B C D E F
-----------------------------
6 - 11 a 1 1 1 1
7 - 22 b 2 2 2 2
8 - 44 d 4 4 4 4

View 9 Replies View Related

Delete The ' Before The Value

Apr 27, 2008

I have some weight scales which i can connect to the pc and download some values into an excel spreadsheet. trouble is in the date column the values in each cell contain a ' before the time & date = '08:50:30 27/04/2008

I want to create a graph which contains the date but in a correct format being mmm/yy

Instead of selecting each cell and removing this ' before the value after each time i connect and download a bulk of data - is there a way e.g vba to remove this ' and convert into a format of mmm/yy

View 9 Replies View Related

VBA - If Then Delete Row

Jun 25, 2008

I am trying to write a code that checks a column, if it doesnt fit the criteria, then delete the entire row.
My VBA is very rusty.



x=6 ( column F)

if len(sheets("sheet1").cells(x,1)

View 9 Replies View Related

To Delete All Except For

Jul 29, 2008

modifying this code to apply to several sheets in the workbook that I specify rather than just the active sheet. Below is the current code.

Sub DeleteAllExceptFor()

'This will delete all row except for the one you specify to exclude.

Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Dim strKeep As String

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
strKeep = .InputBox("What Value should be excluded?")
End With

View 9 Replies View Related

Delete Row IF

Nov 17, 2008

Currently this deletes any row that doesnt have "Date", "Total" or the name of days of week.

I am needing another criteria.

1. need to also NOT delete the row if Col A is a date.
2. Col C - DO NOT Delete if Left(4) is "LINE"

Sub Clean_Up()

'Determine the last used row in column A
lr = Sheets("prod schedule").Cells(Rows.Count, "A").End(xlUp).Row

'turn off screenupdating and events (greatly improve speed)
Application.EnableEvents = False
Application.ScreenUpdating = False.........

View 9 Replies View Related

Delete Row If, And The Row Below

Oct 23, 2009

I've got a spreadsheet of approximately 5000-10000 rows long,

I've got a sorting macro running but now i would like it to do the following:
In Column C are the entries: COR, NEW or REP.

I would like create a macrothat deletes the rows with COR in column C, AND the row below it.

How can i do this? This is what i've got sofar:

' Select sheet Pivot1
Sheets("Pivot1").Select
' Turn off screen updating to speed up macro
Application.ScreenUpdating = False
' Sort data
ActiveWorkbook.Worksheets("Pivot1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Pivot1").AutoFilter.Sort.SortFields.Add Key:=Range _
("D2:D5607"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal..........

View 9 Replies View Related

Add And Delete A Row

Apr 18, 2006

how to add or delete a row based on certain criteria. The criteria would be Customer and CustSet. Whenever the workbook is opened I need to run a check on Customer and CustSet to see if those exist. If those exist in the record then just move to the next record, but if the do not then delete that row. Also, if a new Customer and CustSet is found I need to add a new row. Customer and CustSet are what make each record unique. So, you should never have the same Customer and CustSet for 2 records.

Public Sub MRASScheduleExport()
Dim rsEPN As DAO.Recordset
Dim rsCalc As DAO.Recordset
Dim db As Database
Dim strCalc As String
Dim rngCalc As Excel. Range
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim sngsht As Single
Dim dbfield As Field, qdf As QueryDef
Dim iRow As Integer
Dim dtPlanned As Date
Dim bolMod As Boolean, bolSpare As Boolean
Dim strFile As String, LCell As String, LColorCells As String
Dim sngCustomer As Single, sngCustSet As Single
Dim rsCol As DAO.Recordset
'Open Workbook
strFile = GetOpenFile_TSB(CurrentProject.Path, "Fan Reverser and Kit Schedule", "*.xls")
Set objXL = New Excel.APPLICATION
If strFile = "" Then: Exit Sub
Set objWkb = objXL.Workbooks.Open(strFile)
Set db = CurrentDb

bolMod = True
objXL.Visible = True
'loop through workbook set activesheet
For sngwrk = 1 To objWkb.Worksheets.Count
Set objSht = objWkb.Worksheets(sngwrk)
strqry = "vwMRSScheduleEPN" & objSht.Name........................

View 2 Replies View Related

Delete Row In VBA

May 2, 2006

May I ask how to write a VBA code that can delete row dynamically? Assuming that i have a variable x and delete till row 1000 I have tried this:

rows(x,1000).delete

But it appear object defined error.

View 2 Replies View Related

Ado Sql Delete Rs.delete

Jun 8, 2006

ado sql delete rs when i rs.delete, i get an error message. "Error message—current recordset doesn’t support updating. This may be a limitation of the provider…”

'grab jobs from "que" holding...xls...
Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer, r As Long
strSourceFile = "\03-serverdatadatabaseHolding_Schedule_RG.xls"
strsql = "SELECT * FROM [Jobs$];"
'connection...
Set cn = New ADODB.Connection
'On Error Resume Next
'open connection..................

View 4 Replies View Related

Delete Every Second Row

Nov 27, 2006

I'm taking out every second row in my spreadsheet with the code below, I've tried to write a loop to do the job but it's beyond me.

'hide every second row in cash advances

Rows("74:74").Select
Selection.EntireRow.Hidden = True
Rows("76:76").Select
Selection.EntireRow.Hidden = True

this code continues on like this untill row 118 and deletes all the rows in between. It works ,but it's a bit long, If someone can show me how to write a loop to shorten it.

View 9 Replies View Related

Delete An Old Row

Apr 1, 2007

when i update a row, i want the database will delete the old row and contain the new one.

View 5 Replies View Related

Delete Every Other Row

Jun 26, 2007

I am trying to write a macro to delete every other row of data in a massive set of data that approaches 60,000 rows. So far everything I've tried has caused overload in Excel. The simpler For-Next loops that I write take forever - well, at least 10 minutes.

Sub Del_Every_Other_Row()
For j = 2000 To 60000 Step 2000
Set del_range = Range(Cells(j - 1998, 1), Cells(j - 1998, 20))
For i = j - 1996 To j Step 2
Range(Cells(i, 1), Cells(i, 20)).Select
Set del_range = Union(Selection, del_range)
Next i
del_range.Delete Shift:=xlUp
Next j
End Sub

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved