Run-time Error 438 - When Controlling Remotely
Aug 13, 2009how to fix my excel 2003 run-time error 438 "Object doesn't support this property or method" error? The VBE highlights this line of
View 4 Replies
ADVERTISEMENT
Running A Password Protected Macro Remotely
Jan 26, 2007I have a macro in a workbook (good start) as follows:
Sub UnhideSheets()
' Set to Auto Calc mode
With Application
.Calculation = xlAutomatic
End With
' Asks for a password (must be "unhider")
Dim mypass, response As String
mypass = Application.InputBox("This macro requires you to enter a password to proceed." & vbCrLf & "Please enter password below.")
' If password is correct
If LCase(mypass) = "unhider" Then
' Unhides all sheets
Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Visible = True
Next i
' Then tells you that all is OK
response = MsgBox("All sheets unhidden", 0, "Macro run")
' Otherwise
Else
' Tells you that there is a problem (Unauthorised to continue)
response = MsgBox("Sorry, you are not authorised to use this macro.", 16, "Macro stopped")
' hides the sheet again
Sheets("BSG Only!").Visible = xlHidden
Sheets("CC07").Activate
' And ends the "if"
End If
End Sub
This unhides the "very hidden" sheets that make the book work and is password protected because I want to limit the people who can see this data.
What I would like to do, however, is call this macro remotely from another procedure in a separate book, which can only be accessed by those who are authorised to see the hidden data.
Essentially, then, my question is: "How do I call the protected procedure without the user having to enter the password each time, as they must be authorised to have access to the procedure that calls the protected procedure?"
Run-time Error '91' When 'On Error Goto' And Cells.find
Oct 8, 2008I 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
Run-time Error Every Other Time The Macro Runs
Oct 10, 2006I'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
Run Time Error 1004 Application-defined Or Object-defined Error
Mar 5, 2013I 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.
Run Time Error 1004 (application-defined Or Object-defined Error)..
Apr 26, 2009I 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
Aug 12, 2009Run-time error '1004' Application-defined or object-defined error. I am trying to use this
View 2 Replies View RelatedRun Time Error 1004 - Application-Defined Or Object Defined Error
Oct 4, 2009I was trying to use the below code
View 4 Replies View RelatedError Message "run Time Error '424' Object Required"
Aug 17, 2008For 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?
Run Time Error 1004 (Application Defined Or Object Defined Error)
Dec 4, 2009I 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.
Controlling IE
Nov 29, 2006I am using vba to control internet explorer and return the inner html. To do this I have something like:
Dim arrinnerhtml As Variant
arrinnerhtml = IE.document.body.innerhtml
My problem is that I can’t get the meta description and keywords tags. The title tag seems relatively easy using:
Dim Title As String
Title = ie.document.Title
Is there a relatively simple way to do this for the description and keywords tags? It’d be nice if it was ie.document.description although it doesn’t seem to be. I’m obviously missing a trick here.
Controlling More Than 2 Pivots
Sep 10, 2009Following up on a tread from Sailor64, I tried to use a code DonkeyOte created,
View 2 Replies View RelatedControlling Several Passwords
Jul 28, 2006how to liberate only the sheets already opened by this user, even after he/she has closed the workbook? It means that when this user re-open the workbook, only the password for this step (workbook) and of course for the sheets still closed (not yet visited), will be requested...
There will be about 20 users, accessing the same file (at least 8 or 9 at the same time), and each one has a different level. For this reason the workbook is also protected: depending on the username and password, I'm trying let excel "knows" what sheet each user can open, without type a password again. trying to illustrating: (all users and password are added by me, using a MasterSheet)
user1 ----- already unprotected sheet9 ----- can open sheet1 to 9 (no password, even re-opening the workbook)
to open the sheet10, a username and a password are required
user2 ----- unprotected sheet3 ----- can open sheet1 to 3 (no password, even re-opening the workbook)
to open the sheet4, a username and a password are required
............. and so on.........
Controlling Another Program From Vba
Mar 21, 2007I have data in an Excel worksheet that needs to be entered into a proprietary programme. I believe this is possible using SendKeys but as I need to keep switching back to Excel to get the next bit of data I am unsure as to how to go about this. The program would already be open as it is a dial up situation and I would have to dial into the relevant site first.
View 4 Replies View RelatedControlling Another Application Where Hotkey Is Not Available?
Jan 29, 2014I am working on an Excel macro which uses the Shell function to open another application, the AppActivate statement to change focus to that application, and then a series of SendKeys statements to perform tasks for which keystrokes (hotkeys) are available. However, there is one step in the process which does not have a hotkey available, but requires clicking on a drop-down with the mouse. Is there a way to have an Excel macro "click" on something for which a hotkey is not available?
View 1 Replies View RelatedControlling Drop Down Lists
Dec 31, 2009I would like to do something similar to wiL with an employee drop down list. As the user begins to type the name, the drop down would narrow the choices alphabetically or the user could select the drop down list then hit the first letter of the name and go to that letter of the list (i.e. selecting "M" to go to the portion of the list that starts with "M").
View 2 Replies View RelatedControlling Autocalculate With Macro
Jul 17, 2009I have a spreadsheet with ~30,000 rows of data that is using the "NOW" function. The endusers are having an issue with the spreadsheet having to recalculate everytime they run a filter as the spreadsheet recalculates the formulas everytime.
Is there a way to turn the autocalculate off for this spreadsheet only when it is opened?
I need the formulas to calculate once only when the sheet is initially opened but then turned off after that. When user closes the spreadsheet, the autocalculate needs to be turned back on.
Controlling Multiple Pivots
Aug 14, 2009I am working on an excel sheet (2007) with about 8 pivots from the same data (huge db). The 8 pivots take a different cuts of data. One filter element is same across all pivots. (For instance let us say that filter is City names: with 10 different city values in it- NY, London, Chicago etc)
What I would like to do is have a system where I just chose NY in one place and all the 8 pivots should use NY as one of the filters and refreash the data accordingly.
What I am doing now is I got to each pivot, choose NY first and then refreash all for the data.
Controlling Non-Microsoft Applications With VBA
Sep 7, 2009How can I control non-Microsoft applications using VBA? For example, how can I open a pdf file, print it and then close Acrobat Reader?
View 5 Replies View RelatedControlling Scroll From One Listbox To Another
Nov 18, 2013I have 2 list boxes and want to controll scrool range (View) depending of selection of List Box 2.
Here is my code.
Private Sub UserForm_Initialize()
'ListBox1
Dim lbtarget As MSForms.ListBox
Dim rngSource As Range
Dim arr
Dim I As Long
Set rngSource = Worksheets("Material").Range("F4:K3000")
[Code] ..........
Controlling A Chart In A Macro
Mar 27, 2009In Excel 2007, I'm writing a macro to create several charts (column) that need to match what a designer has already built. I'm having a devil of a time finding the code I need to create parts of the chart. I've searched the web and this forum, but I must just not be asking the right things.
I can see everything I want to do in the format pop-up window when I right click on the charts in Excel, here is the path:
1) Format Minor Gridlines, Line Style, Dash Type, Rounded Dot
2) Format Axis, Axis Options, Major tick mark type, None
3) Format Axis, Axis Options, Position Axis, Between tick marks
Controlling Cursor Movements
Sep 5, 2009I have a workbook that contains spreadsheets that serve as input sheets to generate reports (other spreadsheeets within the workbook). I did not create user forms as I find them cumbersome to do so. Instead I formatted the input sheets to be extremely user friendly. I protected the sheets so that user can only access the unlocked cells that require inputs.
Question:
Is it possible to control the movement of the cursor by both tab and enter like is done in the user forms. The input cells are spread out in various locations on the spreadsheet - therefore, I would like to drive the direction of the cursor as to what cell to go next after an input is made and entered by either hitting the enter button or tab button.
Controlling Toggle Buttons
Apr 26, 2006I have several toggle buttons on a spreadsheet for the user to choose which month they want data for. Can anyone tell me how I can make the toggle button that is in the down position change to the up position and not execute the command again when one of the other toggle buttons is pushed?
View 6 Replies View RelatedControlling Cell Size
Feb 27, 2007Imagine a rectangular box, with a horizontal line across it, drawn using excel cells.
This is a representation of a screen that comes in 2 sections - sometimes the upper section is largest, sometimes the lower. (Sky above, land below for example)
Is there a way to draw and control the position of the line in this box, , so that by adding a numeric value in two adjecent cells, for example 75 25 would give more "sky" (the line towards the bottom) and 25 75 would give more "land" (The line towards the top)?
Run Time Error 91
Oct 21, 2008run time error 91 ...
View 14 Replies View RelatedRun Time Error 438
Nov 12, 2008I have the following code in a macro and when it is run I get a Run Time Error 438 Object doesn't support this property or method. This occurs at the first occurrance of the destination/source.
View 5 Replies View RelatedRun-time Error 424
Feb 26, 2009I have the main form completed and everything appears to be in order. So I made a button on the first sheet that simply calls for the main form to be shown. However, every time I click it, I get the run time error 424 object needed thing. I don't understand because the button is calling the form and the names are all correct. When I click debug, it takes me to the small code for the start button. Below are the codes for the start button and the main form.
View 10 Replies View RelatedRun-time Error '13':
Dec 11, 2009I have a line of code to enter a date on a spreadsheet from a user form:
View 2 Replies View RelatedRun Time Error 424
Jan 25, 2010Getting a run time '424' error with following code (object not defined). Not sure if I have it set up correctly.
View 6 Replies View RelatedRun-time Error
May 4, 2007I am getting Run-time error '91':
Object variable or With Block variable not set
In my mind everything checks out, but I am still very new to all this.
Here is the
'Shifts cells to make space for inserted VFD
Dim CheckBoxes As Integer
Dim UpLeft
Dim LowRight
Dim CountCells As Integer
Dim MoveTo As Range