I'm getting a 'Run Time Error 91' saying that 'Object Variable or With Block not set'.
Error lies here:
Code:
Me.lstCategpry.Column = CustRec.GetRows(Fields:=Array("name"))
Userform Initialize:
Code:
Private Sub UserForm_Initialize()
[Code].....
I am getting a run time error # 9 when I run a macro that calls a Userform or when I try to run code in a Userform module. The code performs beautifully on my computer, but it did not work on a coworker's computer. It ended up working on 3 out of the 5 computers I have tried it on.
I have tried changing security settings to low, and a bunch of other stuff, but I cannot get the code to run on the computers that get the run time error on them when I try running the code on them.
I get the run time error when I try to load or show any userform in the workbook and I get it if I try to run code that is in the userform module. However, if I paste the code into a regular module and run it, the code runs fine.
Does anyone know what could be causing this? I don't think my code is causing the problem since it runs on some machines, I am guessing there is a setting that is preventing Excel from calling Userforms.
I have a button, Private Sub CommandButton1_Click, on a sheet that shows a userform. This is the bit of code that gets tagged with the run time error. The userform has a refedit control on it allows the user to select a cell and then hit ok to run the code or cancel to, well, cancel it.
Code for the button that gets tagged with the run time error:
Private Sub CommandButton1_Click()
frmLoadTrade.Show 'calls userform
End Sub
Code in the Userform Module:
Private Sub cmdCancel_Click()
TradeTicketSpreadsheetName = ThisWorkbook.Name
Unload Me
Workbooks(TradeTicketSpreadsheetName).Activate
End Sub
I included a couple screen prints showing that the form exists, and that it is not misspelled in the code. What should I look for?
View 4 Replies View RelatedI need to make the Red X at the top firght of my user form "End" rather than "Exit Sub" which I think it is trying to do now, as simply exitting the sub returns it to the previous sub which opens the form again.
View 4 Replies View RelatedTrying to convert an Excel 2003 macro to work in Excel 2007.
The problem line is
Dim MyDataObject As DataObject
I suspect the problem is a Missing Reference, but I cannot figure out which one. I have the same ones (in 2007) as 2003 except for one which is not showing
Microsoft Forms 2.0 Object Library
Is this the one it needs? It is called something else in 2007?
The ones I do have ticked are
Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.8 Library
Microsoft Scripting Runtime
Microsoft XML v2.6
I get a "Run-time error '9': subscript out of range" error when calling a userform.
The code opens a reference spreadsheet then shows a userform with a combobox and two command buttons (Continue and Quit). The userform has a private initialization sub-routine to populate the combobox with values from a list on the reference spreadsheet. When the userform is called the error comes up.
Here's my problem: The code was written on my computer and saved as an Add-in. The add-in has since been installed on a number of other computers and works properly. Recently I tried to run the code and this error started popping up, don't know what changed. All versions of Excel are the same and haven't changed.
BRF = ActiveWorkbook.Name
If BRF = "Blank upload.xls" Then
Workbooks.Open path & "QC Data AnalysisQCDA ref.XLS"
OtherBRF.Show
Workbooks("QCDA ref").Close SaveChanges:=False
Goto 1
End If
the spreadsheet needs to be copied to a directory called "C:downloads" as it contains a ODBC query to itself (In reality, this is a query to an External Oracle Database)
On loading, it should pop up a simple userform, with a combo and two command buttons, which when pressed takes you to a (hidden) tab that displays a pivottable.
All works well until I try to close /save when 60% of the time, Excel encounters problems and closes and will not load up the file the next time until either quit excel or disable macros. Messages include "file/path access error", "I/O Error" or get restarts excel.
On a casual run through, I expect you might report back that "All worked ok for me". Please can you give it a bit of a thrashing, comment out the userform show, save the file (frequently) becuase i assure you it will break ultimately!
This is a brand-new file and I've tried it on about 5 different PC running different versions of Excel and generally get the same result.
I have written a Excel (2003) that searches a worksheet for a string in any cell. If the string is not found, it uses the 'On Error GoTo' command to jump to a given label. It works fine on the first string not found. When it searches for the next non-existent string, it fails with:
'Run-time error '91':
Object variable or With block variable not set'
Do I have to clear a buffer after each cells.find search?
My
I have a userform that has a time field on it and then the userform displays the time as a number.
Ex. I entered 11:00 AM in the Time of Contact field and the form displays it as 0.458333333333333.
I have a userform that time stamps on my userform as soon as i open the form, is there a way that when I submit, that the amount of time that I was on the call to be put in my column on my worksheet as minutes?
View 2 Replies View RelatedI am using the following array formula "=SUM($I$35:$I$1000*($G$35:$G$1000=$B$10))" which works fine but if there is nothing to sum it returns "0", but I would prefer a blank cell if there is nothing to sum.
View 3 Replies View RelatedI have a client that enters a lot of data into various spreadsheets, we recently upgraded her computer and along with it upgraded Office to 2013. Since that has happened she has experienced some issues with navigating cells in Excel. What will happen is she will be typing than tabbing or using the arrow keys and all of a sudden the active cell will get stuck on one of them. Arrow keys do not move the cursor to the next cell and the only way for her to fix it is to alt tab to another spreadsheet and then back to the one she was working on. Once that is done it is working fine.
There seems to be no rhyme or reason to when this happens to her. I have re-installed (used the big one that re-downloads from the web), it has no add-ins on either that are conflicting and I know it is not the keyboard since when I log in through TeamViewer while it is happening I am also unable to move with the arrow keys.
For some strange reason the workbook I have been using on a daily basis has suddenly decided to go into design mode and stay there. No matter how many times I click the design mode button in and out it always stays in and I can't use my buttons.....it just selects them?
View 9 Replies View RelatedI am currently stuck with a drop down list problem. I am trying to make a benchmarking solution for a group of 20 stores. All stores sell products uniformly and their sales are displayed in percentages of the cumulative total. Currently there is a benchmarking page where all stores sales per product is displayed, however I would like the ability to select specific stores data from a drop down list. I have pictures displaying the basic concept of what I want to achieve.
stores.PNG
On the left a list of products and the quantities sold in percentages per store for benchmarking purposes.
What I would like to achieve relates to the drop down list on sheet 2.
store 1.PNG
Here store 1 can see its sales in percentages, and has a drop down list to compare to different stores. If store 3 is selected I would like the data to be pulled into that column from the benchmarking sheet and the same goes for the other stores of course.
I seem to be stuck in "Full Screen" mode. When I go to the View menu, both "Full Screen" and "Normal" are highlighted. I can't seem to get out of Full Screen.
I am using Excel 2003, XP SP3 installed. This just started this am. I did download and install Microsoft patches this am. ???
I am able to save and use Excel but without normal view my formating buttons etc. are not available.
I'm not sure why this is happening, but every other time I run this one specific macro, I get a "Run-time error '1004': Paste method of Worksheet class failed". I even tried running this macro, then running a different one, then running this again, but I still got the error every other time.
Every time I get the error, it highlights this line of
Sheets("Regenerate Request").Paste
This is all of the code up to where I get the error:
Sub YesRegen()
' after user has hit Yes on the RegenerateRequest macro, this posts the new request to
' the log, generates the new file and attaches it to an email
Application.Run "LogUnprotect"
Range(Range("A" & ActiveCell.Row), Range("K" & ActiveCell.Row)).Copy
'Selection.Copy
Sheets("Regenerate Request").Activate
Application.Run "RegenFormUnprotect"
Range("A40:K40").Select
Range("A40").Activate
Sheets("Regenerate Request").Paste
I have following data on spreadsheet, which I need to import into other place but I would need to reorganise rows and columns first.
Product1-Sep2-Sep3-Sep4-Sep
SKU1112345
SKU22212131415
SKU3339876
SKU4444444
Basically I would like to have this data in 3 columns like this:
DateSKUOrders
1-SepSKU1112
1-SepSKU22212
1-SepSKU3339
1-SepSKU4444
2-SepSKU1113
2-SepSKU22213
2-SepSKU3338
2-SepSKU4444
3-SepSKU1114
3-SepSKU22214
3-SepSKU3337
3-SepSKU4444
4-SepSKU1115
4-SepSKU22215
4-SepSKU3336
4-SepSKU4444
I tried transpose etc. and not able to figure it out. Is this something that can only be accomplished with VBA?
I have the following code that gets stuck in the Loop and will not stop unless I press the Esc key:
Sub WIP()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long
Dim newRow As Long
Set ws1 = Sheets("PAYCALC")
Set ws2 = Sheets("WIP")
Application.ScreenUpdating = False
With ws2
.Range("A2:C" & .Range("A2:C2").End(xlDown).Row).Clear
End With
x = 10
lastrow = ws1.Range("C5").End(xlUp)
Do
newRow = ws2.Cells(65536, 1).End(xlUp).Offset(1, 0).Row
ws2.Cells(newRow, 1) = ws1.Cells(x, 2).Offset(-2, 0).Value
ws2.Cells(newRow, 2) = ws1.Cells(x, 2).Value
ws2.Cells(newRow, 3) = ws1.Cells(x, 2).Offset(3, 0).Value
x = x + 21
Loop Until x >= lastrow
There's a twist to this. I have the same identiclecode in a different module. The only difference is ws2 is different and there are 7 newRow's. Other then that the two are the same.
That one works just fine without getting stuck. It will go through 200+ sets of records and stop when it reaches the last one.
The one above will not stop even though I just put in three records on ws1 for testing.
=IF(M7="","",IF(K8=J8,K8&$J$3&I8,IF(K8=I8,K8&$J$3&H8,IF(K8=H8,K8&$J$3&G8,IF(K8=G8,K8&$J$3&F8,K8&$J$3&J8)))))
I have tried several options, ISERROR(FIND(.................also EXACT(........ and they all seem to get stuck, after the second expression.
Basically, I have a ROW of Cells, with numbers inside them, like so:-
F8.....G8....H8....I8...J8....K8
27.....33....10.....4....4.....4
And looking from right to left, I am asking this question:-
If K8=J8, then I will have K8 & I8 as my result.
If K8=I8, then I will have K8 & H8 as my result.
If K8=H8, then I will have K8 & G8 as my result.
If K8=G8, then I will have K8 & F8 as my final result, Else its K8&J8 as my FALSE.
(the $J$3 = a comma, for number separation, and further LEN string capture.)
Every time I have made this formula, with other types of Logic, it only ever finds the second tier depth as the result,
and I wonder why, since they are several nested IF statements in line!!
The incomplete Excel sheet is attached : S 1_2 tracker Forum.xlsx
The result of the above formula should be 4,10
But instead I get 4,4
I am supposed assign macro to track all employees hierarchy in an organisation.
In Sheet1 I have Employee ID's in D Column and Supervisor ID's in N Column.
And In Sheet2 I assigned macro...
What exactly I need is when I give number and click the button I need all the employee id's in his hierarchy
Concept:
The number which you give should search in N Column and Display D Column Values...and those D column values should search in N column and display D column values....so on.....
So far, it is showing 1 level hierarchy...but I need till end...
I have a bar graph in excel 2010 and the vertical access is stuck in the middle of the graph. When I right click on the axis and select "format axis" I have an option to set where the horizontal axis crosses but never the vertical.
View 1 Replies View RelatedI am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
I have a relatively complex report that I work with and a worksheet is no longer required. I have deleted the worksheet and reference to it hwoever when running the macro to pull all the data, it gets to the summary of all the data and i get the Run Time Error 1004 Application-defined or object-defined error pop up. ON reviewing it, it is on this line ActiveCell.Offset(0, 0).Range("a1:a" & Range_Height).Select of the below code...
VB:
Sub GetRangeName()
Sheets("TOTAL").Select
[Code].....
use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window.
I have a simple function below to put in different forumlas in different cells to get stock quotes. When I run this I get runtime error 1004 application-defined or object-defined error. The first formula goes through but vba chokes on the next formula: ActiveCell.Offset(I - 1, 4).Formula = username
Run-time error '1004' Application-defined or object-defined error. I am trying to use this
View 2 Replies View RelatedI was trying to use the below code
View 4 Replies View RelatedFor some reason my form won't open when the workbook is opened. I get an error message "run time error '424' object required" (which happens when I have Form1.show in the BOTH workbook_open event and the userform_initialize event (oops)). When I removed form1.show from the userform_initialize I don't get an error but I also get no form. I recall having this issue before but I can't recall how to fix it.
Can I call the userform_initialize event from the workbook open event to get around this successfully and properly?
I keep getting a Run Time Error 1004 (Application Defined or Object Defined Error) when my sub reaches this line:
ActiveCell.Formula = "=SUM(D222,D224,D226,D227,D229,...)"
In the actual line of code the "..." above is another 20-30 or so cells in column "D". Probably no more than 150-170 characters in the line.
If I remove half of the cell range names it works, but I need all of the cell ranges for the equation.
have a look at this and tell me why i cant even give it a trail run without getting an error
[url]
I keep getting an Run-time error '424': Object required error whenever I try to run a macro with a rather involved user form. I know the code for the form is what is keeping it from running properly. Here is the code for the form:
View 6 Replies View Related
