Function Call To Set The Password Of The File
Apr 23, 2007
I found a function to set the password a an excel file:
=========================================
Function SetPwd(strNoPwdFile As String, _
strPwdFile As String, _
Optional strOpenPwd As String, _
Optional strModPwd As String) ' As Boolean
' This function requires the following arguments:
' strNoPwdFile - The path to a document without a password.
' strPwdFile - The path and name to save the password-
' protected document.
'
But the compiler said that it is syntax error.
How can i rectify the error?
View 9 Replies
ADVERTISEMENT
May 21, 2006
I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
View 4 Replies
View Related
Jun 23, 2014
I trying to create a paperless petty cash system. I have my sheet all setup and calculating how I want. When a user makes an entry they Enter their name in column "W".
I am trying to overcome the problem of accountability. I need to achieve a cell calls for Username and Password and if correct the persons name is entered into cell then Row is locked to prevent figures being altered etc. But also if user made mistake they need to unlock it somehow someway
Sure this achievable from snippets of info I seen online but its way beyond my abilities. I realise a determined user can circumvent this if they wanted to.
View 5 Replies
View Related
May 27, 2009
When opening a new Excel file, it is asking me for a password to an old excel file that was password protected, which I have deleted from my computer. It is still asking for this password every time I open a new or existing file. When I hit cancel it lets me open the file.
View 11 Replies
View Related
Dec 9, 2006
I have an existing application that uses DAO sql queries to populate a form. It has always worked fine. Earlier this week, I password protected the spreadsheet. Now my DAO query fails. I get a run-time error '3161'. Could not decrypt file.
' Const stExtens As String = "Excel 8.0;HDR=Yes;"
Const stExtens As String = "Excel 8.0;HDR=Yes;PWD=money16;"
Private Sub Execute_Query(SQL)
'Instantiate the DAO objects.
strDb = ThisWorkbook.FullName
Set DAO_ws = DBEngine.Workspaces(0)
' code fails on the line below
Set DAO_db = DAO_ws.OpenDatabase(strDb, False, True, stExtens)
Set DAO_rs = DAO_db.OpenRecordset(SQL, dbOpenForwardOnly)
End Sub
View 3 Replies
View Related
Jun 19, 2014
I have an add-in called GeoDesiX. It is a geocoder/mapping tool that uses Google Maps API. It works fine, even on my machine, but I think it was built using 32-bit, while I have 64-bit Excel. When I open the add-in for the first time, an example workbook is below, it works great.
The problem is, that when I save any file with the map in it, the VBA no longer works, and an error pops up in the below code that says: "Object Reference not set to an instance of an object". This happens at the Err.Raise 65000, "GeodesiX", Result part. It looks like it just cannot find the DLL file, which is the "GeodesiX" in that error part. I was reading that it might be caused by switching between 64 bit and 32 bit, but could not figure out how to do it. I am guessing you need to put Private Function Declare PtrSafe, but I could not figure out syntax on how to get that to work. I understand that you will not be able to use these as it is an add-in, but maybe you just know what I need to do in order for the vba to call to the DLL file.
View 1 Replies
View Related
Aug 30, 2007
Just wonder how to call the function if I have it's name in the variable String
for example something like that:
View 11 Replies
View Related
May 10, 2007
I wrote a function procedure in VBA. Pasted it into a 'VBAProject' sheet in my workbook. But when I try to call the function (by entering the name and arguments into a cell), the sheet displays "#NAME?".
What have I forgotten?
example:
Public Function TotDays( _EndDate As Date, _StartDate As Date _) As Integer
TotDays = EndDate - StartDate
End Function
cell
a5: =totdays(b2,A2)
b2: 4/1/2007
a2: 3/1/2007
View 9 Replies
View Related
Jul 12, 2007
I created a file with one sub and one function. Saved it as an add-in. and followed the promts to insert the Add-in.
The button I assigned to the macro works fine, but when I call the function, I am told "Sub or Function not defined".
Is there some special way to call an add-in function from VB?
View 9 Replies
View Related
Sep 24, 2008
How is it possible to take the choice from the Combo Box and send that into a Stored Procedure statement in Excel VBA
I have 3 Combo Boxes, Months, Years, Date.
I'm trying to fillout these into this statement to run my Stored Procedure.
View 10 Replies
View Related
Mar 2, 2012
I have found some code that does what I want- but i do not understand how to use - call it. The programmer says ...
GetNetworkIPAddress()
' can be called from a worksheet cell using the formula:
' =GetNetworkIPAddress()
I have put this in a cell but i ger an error #Name?
what should i do ?
View 9 Replies
View Related
Aug 12, 2008
I'm playing around with the Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) function. I have a list of people in Column D that I'd like to move data around if the user double clicks on a cell in Column D. The list is about 158 rows long, and could grow beyond that. I've gotten lazy typing:
If Intersect(Target, Range("D4") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
If Intersect(Target, Range("D5") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
Etc, Etc, Etc
What I was hoping to do is use a variable (t) to cycle through the rows and call the various functions. So what I came up with is:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Dim t As Integer
Dim Subtest As String
For t = 4 To 158
Subtest = "D" & t
I've created Sub D4(), Sub D5(), and Sub D6() as a test. This didn't work at all as it's looking for a Subtest(). Also, clicking on D5, D6, didn't do much either. Is there any way to make this cycle work, or should I keep typing away?
View 9 Replies
View Related
Aug 23, 2006
I wanted to have that one appear whenever a certain cell in a certain sheet gets a specific value, and depending on the answer of the user the Form should set the value of some other cell. So maybe there´s a much simpler solution to this task than what I tried - and I´ll be grateful to learn about it.
Anyway I tried the code which follows below - and it works just fine when I call it from the VBE environment - but inside the UserForm it just stops in between (without any error message) when I operate it from the Excel sheet (but still succeeds with the function). I´ll be very happy if someone could solve this "miracle" for me (even IF there´s a better solution altogether). So I just provided a simple Function which does nothing but call the UserForm and is executed whenever the condition is met (some calls to MessageBoxes in between just have been added to see how far the code gets executed): Function:
Public Function Call_my_form()
Call Option_Form.Show
MsgBox "Call executed"
End Function
Call of the function: =If(E11="Go";Call_my_form();"Nothing")
The UserForm just uses two OptionButtons with code as follows (two versions to check whether one of these would be the reason - both do behave the same way):
Private Sub OptionButton1_Click()...........................
View 5 Replies
View Related
Apr 3, 2008
I have created an Excel add-in (.xla) which contains many custom functions. This add-in is stored as read-only on a network drive. I reference it from various spreadsheets and calls to it work perfectly unless I put a call to it in the Workbook_Open event. When I make a call to a function in the add-in from a spreadsheet's Workbook_Open event, Excel crashes completely and I get the dialog that offers to recover the file and report the problem (i.e. Excel shuts down). I have no doubt that this is being caused by the call to the add-in specifically from the Workbook_Open event as removing this call solves the problem. Also, it is not related to a particular function as I have tried various ones; it is ANY call to the add-in from Workbook_Open that causes it. Is this a bug in Excel or is there something that I need to do before referencing the add-in in Workbook_Open? I have tried Application.CalculateFull and Doevents prior to making the call to the add-in and neither approach worked.
View 2 Replies
View Related
Oct 1, 2008
I'm trying to call a sub in an exce. file that resides on our network drive so that multiple users can access the programs that I develop and I can get VBA to open the file but I can't get it to open the sub titled "DCU". I keep getting the following error.
Cannot run the macro DCU. The macro may not be available in this workbook or all macros may be disabled.
Here is my code that errors.
Option Explicit
Sub DESCRIPTION_CLEANER_UPPER()
Application.Visible = True
Workbooks.Open Filename:= _
"\Cansvp01grp_01fCommonCommon-PartsPrcngMacrosMacros.xls"
Application.Run "DCU"
End Sub
View 9 Replies
View Related
Dec 22, 2009
I'm using an activex combobox in excel. It display 4 posible choices, and one of them is "Other". When someone select "other" there is a textbox named "tb" that should be filled. Otherwise tb should be invisible. Combobox change() function looks like that:
View 14 Replies
View Related
Jan 9, 2006
I have a spreadsheet with 5 worksheets representing the working days of the week. Each worksheet contains data pertaining to every occurance of that day for as long as it has been recorded. Each day is broken up into hourly data.
So for example, for Monday's worksheet, you would have
Col A..............Col B........Col C
9/jan/06........5am.........data
9/jan/06........6am.........data
9/jan/06........7am.........data
...etc....then...
2/jan/06........5am.........data
2/jan/06........6am.........data
2/jan/06........7am.........data
...etc....
We are trying to create a function, on another worksheet called Query. The only way I can describe what we want it to do is to do it in psuedocode.
Psuedo........................................
View 10 Replies
View Related
May 23, 2006
I want to call on a function with a macro/button in the same way as when you choose "insert-function" but in a way that the user himself is able to put in values on the spredsheet.
View 7 Replies
View Related
Mar 27, 2008
I have a written a small sub to run solver. However, I need to run a secondary sub at each solver iteration. The secondary sub I want to run is called “BypassGoal”.
My code currently looks like:
Sub OperatingPt()
Dim WrkBk As Workbook
Dim WrkSht1 As Worksheet
Set WrkBk = ThisWorkbook
Set WrkSht1 = WrkBk.Worksheets("Engine")
Dim Mode As String
Dim CellTarget As String
Dim CellRange As String
WrkSht1.Activate
WrkSht1. Cells(30, 2).Value = 2
CellTarget = "H14"
CellRange = "B30"
SolverReset
SolverOptions Iterations:=3000, Precision:=0.000001, AssumeNonNeg:=True, Derivatives:=2, StepThru:=True ....................
View 2 Replies
View Related
Sep 30, 2008
I'm trying to put some visual basic out on the network drive at work so I created a book and called it Macro.xls. I then saved the code within that book and saved it out on my network. I'm now trying to run that code by calling the sub and don't know how to do that.
View 7 Replies
View Related
Jun 25, 2013
When I step-through my code below, it always opens the first file in the directory "C:Pyramid Files", but when it comes back to the Pyramid Files sub after fully processing the first file via various other subs, the VB Editor apparently doesn't like something about this line: StrFile = Dir(), since it quits after "snapping-back" to the previous sub Initialize(). I have also tried StrFile = Dir, but that doesn't work either. I did Dim Strfile in the General Declarations. When I set Watches for Dir and Dir(), I get the value "Invalid procedure call or argument" for both, as if the directory function lost the value. I can't determine why this is happening.
VB:
Dim WSM As Worksheet, WSB As Worksheet, WS1 As Worksheet, [U]StrFile As String[/U], StrDirectory As String, ClientCode As String
Dim Filename As String, LastRowb As Long, LastColB As Integer, LastRow1 As Integer, NextRowC As Integer, x As Integer, y As Integer
[Code] .......
View 4 Replies
View Related
Aug 30, 2012
This error message in line vpp:
Invalid procedure call or argument
Code:
Function fn1(ByVal a, ByVal i, ByVal e, ByVal N, ByVal w, ByVal ta)
Pi = Application.WorksheetFunction.Pi
mhu = 398600
vpp = (mhu / Math.Sqr(mhu * a * (1 - e ^ 2))) * (-Math.Sin(ta * Pi / 180))
fn1 = 2 * vpp
End Function
View 9 Replies
View Related
Jan 25, 2008
I have a spreadsheet that uses a function defined in VBA code that is housed in an add-in which is stored in the default location (C:Documents And Settings{user_name}Application DataMicrosoftAdd-Ins). To share this spreadsheet, I first install the add-in on the other person's computer. Then, when the other person opens the spreadsheet, the function doesn't work because the function call on the worksheet contains the whole path of the add-in (and the path contains my {user_name} rather than the other person's). So, the immediate solution is to just delete the path of the add-in from the function call (so that only the name of the function remains, plus the arguments of course). But this is quite inconvenient given the number of times the function appears in the worksheet, and it's not practical as we continue to share files like this.
My question is: is there a way to make sure that the 'C:Documents...' never gets added to the function call in the first place? I mean, why doesn't Excel just try to run the function locally before it adds that path that refers to a separate computer? I do realize that there are other methods of sharing VBA code. For example, I know that I could include the code in the specific spreadsheet that I'm trying to share, and this would work. But, that's not the answer I'm looking for (for one, the function refers to a very large amount of data stored in the add-in, and there are other reasons that make the aforementioned method impractical). I'm wondering if I'm missing something, and if there is a way to stop Excel from tacking on the path string to the function call, so that the function would just work on any computer as long as they have the add-in installed?
View 9 Replies
View Related
May 25, 2006
I have written a public function inside a class module. Is there anyway I could call the public function in Excel? What is the exact syntax to do it?
View 5 Replies
View Related
Aug 2, 2006
I am working with several columns of data, and each column's range is stored in a range array. My program needs to use a specififc combination of the columns as the second argument sent to the regress function. In certain cases, the columns I am referencing are not contiguous, so the function won't work. Here's an example:
Suppose we're working with 3 independent variables (a simple case) whose data is stored in three contiguous columns named x1, x2, and x3. The program I've written will call the regress function 6 times: once using x1, once using x2, once using x3, once using the union of x1 and x2, once using the union of x1 and x3, once using the union of x2 and x3, and once using the union of x1, x2, and x3. Suppose the range for x1 is d5:d18, the range for x2 is e5:e18, and the range for x3 is f5:f18. Also, each union of ranges is stored as one element in an array called Combos, which is sent to the regress function.
The regress function returns an error when prompted to calculate regression stats for columns x1 and x3 because they aren't contiguous. I need to know if there is some sort of way around this. write a procedure that will detect if the ranges being input to the regress function are contiguous, and if they are not, copy them to a new worksheet so they will be contiguous and then send the new range references to the regress function for calculation purposes? Is there an easier way around this issue? If I can't get this to work, my entire program is wasted.
View 3 Replies
View Related
Dec 18, 2006
i'm trying to call a function from another one, i'm getting this error 'Compiler error: = expected' but i don't know the reason, the functions simply take some values an store them in an here is the
Dim productos(19, 3) As String
Sub agregarProducto(ByVal descripcion As String, ByVal modelo As String, _
ByVal precio As String, ByVal unidad As String)
Dim r As Integer
For r = 0 To 19
If productos(r, 0) = "" Then
productos(r, 0) = descripcion
productos(r, 1) = modelo
productos(r, 2) = precio
productos(r, 3) = unidad
End If
Next
End Sub
Sub agregarProductoTelas()
Dim descripcion, modelo, precio, unidad As String
If Selection.Column = 1 Then
descripcion = Selection. Offset(0, 0).Value
modelo = Selection.Offset(0, 0).Value
precio = Selection.Offset(0, 3).Value
unidad = Selection.Offset(0, 2).Value
agregarProducto(descripcion, modelo, precio, unidad) 'error happens right here
MsgBox (descripcion)...
View 3 Replies
View Related
Dec 9, 2011
I'm working on my first VBA project that requires 12 fields to be manually input of which one field is a file path. I need this path for multiple reasons, but the one reason I'm having a problem with is using the location of the path in conjunction with calling a bat file or executing a cmd prompt that is at that path.
Path Location, minus file name:
Code:
sheets("b. Fill Out Required Info").Select
Range("B18").Select
Actual Path found in B18:
C:UsersNickDesktopSubmission ToolTest Files
File attempting to execute:
CopyFileNames.bat
I can manually put all of this together and get what I want by using:
Code:
Shell "C:UsersNickDesktopSubmission ToolTest FilesCopyFileNames.bat", 1
The problem is, every time this will be used, a different path will be used, but the same batch file name will be used. So, rather than having to manually input the file path every time, is there a way to replace "C:UsersNickDesktopSubmission ToolTest Files" with the cell reference of the path listed above? I've tried different methods of this but I cannot seem to get it.
It would be even better if I could just use the cmd prompt found within the batch and run it directly from vba; the prompt I'm running is as follows:
Code:
cd C:UsersNickDesktopSubmission ToolTest Files dir /b/o |find ".xml">ListOfFileNames.txt
How can I put that cmd into VBA? I tried different versions of this with no success:
Code:
Shell "cmd.exe /c cd C:UsersNickDesktopSubmission ToolTest Files dir /b/o |find ".xml">ListOfFileNames.txt"
Notice that the path is the same as the path shown in B18 above. It would be great if that path would be dependent on the cell referenced above.
View 9 Replies
View Related
Sep 7, 2013
In an Excel sheet, in A1 I have written B1 and in A2 I have written D20.
I have a data panel in B1:D20.
I would like to call a vba function my_Fun that acts on my data panel and returns a double in cell A3.
Instead of writing my_Fun(B1:D20) in A3 I would like to be able to write my_Fun(A1:A2), where the entries in A1 and A2 are called, rather than A1 and A2 being used to define the range themselves.
View 5 Replies
View Related
Jan 10, 2013
I try to call a function/procedur in an Excel2010-AddIn (.xlam file) from another Excel2010-AddIn (.xlam file):
Code:
Application.Run (filename.xlam!functionname)
Both AddIn-files are in the same directory (the default AddIn directory).
This call causes the error message "runtime error 91: object variable or with block variable not set".
This problems came up with providing an existing Excel2003-AddIn for Excel 2010.
The same code runs in following cases:
1. call a function in an Excel2003-AddIn (.xla) from another Excel2003-AddIn (.xla)
2. call a function in an Excel2003-AddIn (.xla) from a Excel2010-AddIn (.xlam)
View 1 Replies
View Related
Apr 27, 2012
I would like to have something that will auto run the code in Module1 when the Excel File is opened, but I do not want to code tansfered into the file when it is saved in its location.
I don't mind if the code is tranfered to saved file if in fact I can make sure the code is not run when the new file is opened...
Everything is working as intended at the moment, but when openeing the saved file the entire macro tried to run again. This is what I do not want...
I currently have the following code in Module1.
Private Sub Workbook_Open()
'
' Format_Copy_&_Paste
'
Application.OnKey "^+r", "Warehouse_Cost_Summary"
'
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DataSet"
[Code] ..........
View 4 Replies
View Related
