Is there a way to prevent access to the code of a macro? I am interested in sending out some macro's, but would prefer that the user only be able to run them, and not be able to access the code via the edit button. Curiously, there have been occasion's where I wanted to edit my own work, but the edit button did not function, so I had to exit and start over to proceed. From this I'm guessing there must be a way.
View 2 RepliesThe current code is not working.
View 1 Replies View RelatedI'd like to know if there is a change event that only occurs when a target cell is changed by the user, but does not occur when i'ts changed by a macro. nfortunately, the Worksheet_Change event occurs in both cases.
View 2 Replies View RelatedI have to issue template workbooks to people for budgeting purposes.
Within the workbooks are various numbers of worksheets pre -formatted and ready for these people to enter data.
Some of the data in the worksheets is important to them as individuals but not to me so I have a series of macros that lift the information from the worksheets and put it into a worksheet more specific to me.
Because the users are on the whole not that good with Excel I have put in easy to use look up tables and various proctections to stop them adding or deleting rows or columns as this plays havoc with my macros.
There is one thing I have "so far" been unable to do and I wondered if anyone could help.
Is there a macro that I could put somewhere in the workbook which would detect when someone tried to cut and paste and would either put up a message box or stop them doing this?
The reason being they are cutting from one row to another and this is messing up the calculations which are protected.
Is there also a way to stop them changing the name on the sheet tab in the smae way?
I am attempting to run a MS Ecel macro that is stored on a MS Excel shared file in a MS Ecel target file (locally stored on my C drive) from a VBA module in Ms Access. That is, from a code i want to open the .xls file that houses the macro, and then open the .xls file that i want to run the file in, and then run the macro.
Here is my situation: the file that houses the macro has the workbook hidden and causes in error.
if i unhide the workbook i get a Run-Time Error 91...object variable or With block variable not set.
if i hide the workbook the vba coding can't find the macro...run time error 1004
once again, i currently have an excel file that houses macro whose workbook is hidden.
does anyone know how this can be accomplished?
here is the ....
"Prevent Outlook Access Request" but it kept binning the Request Part. So sorry in advance for the misguidance.
Second up :-
when i use the below code
Private Sub CommandButton22_Click()
Sheets("Sheet1").Select
Range("A1:Z1").Select
Selection.Copy
Workbooks.Open ("\ifdata002opsqual$Quality_and_RiskLive LogLive Log2.xls")
If ActiveCell.Value = Empty _
Then ActiveCell.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False _
Else:
Do Until ActiveCell.Value = Empty
ActiveCell.Select
ActiveCell.Offset(1, 0).Select
Loop
Outlook always asks "A program is trying to access Outlook - do you want to prevent access or allow" (or words to that affect) then you choose a time you allow it access.
Is there a way to prevent that daft wee box appearing - is this an Outlook issue more than excel? If it is outlook is there any way i can code it into excel to prevent the request coming up?
I've developed a little software using Excel Macros & VB. To prevent people from accesing the code I protected the code blocking it from visualization. It seems not enough as an acquaintance of a friend cracked it in 25 minutes. Or so he says. So I'd like to know if there is a better way to protect the font code.
View 8 Replies View RelatedIs there a code that can be input to prevent a document from being printed?
View 8 Replies View RelatedI have a workbook that contains macros. When the workbook is protected, the macro returns an error so I have unprotected the workbook but, if someone protects the workbook and saves it, it returns an error when closing and even if they choose cancel, it still screws up the macro and prevents it from working upon reopen.
Is there a way to prevent someone from protecting the workbook in vb code?
Some code that would prevent my user form (calendar) from popping up when the worksheet is protected?
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$G$8" Or Target.Address = "$G$14" Or Target.Address = "$G$17" Then
CalendarFrm.Show
End If
If Target.NumberFormat = "m/d/yy;@" Then
[Code] ........
I know how to enable/disable events using VBA code, however is there an option within excel to turn it on/off? My problem is this...
At the beginning of my code I disable events and at the end I enable it again (I need to do this to avoid being caught in a loop). However something is going wrong somewhere in my code and the code stops halfway through. I'm trying to test sections of the code, but I often inadvertently stop the code without enabling the events again. Therefore I can't get my VBA to execute again unless I close excel down and restart. This is a pain as I have to find my place in the code again!
I have a spreadsheet with a number of fields on it which must be filled in before the file can be saved, and if the user attempts to close the spreadsheet without filling in the required fields, I want a message box to appear asking for confirmation that the user wishes to quit, if yes, the sheet exits, if no, the sheet remains open
View 2 Replies View RelatedOver the weekend I did some edits to an Excel file on my home computer. This morning I did some edits to the file on my work computer. When I try to access the VBA code, I get the message "Error in loading DLL", which is error 48.
View 10 Replies View RelatedIs it possible to use vba code to run a report in Access and then save it as a PDF?
I have a subroutine (well, a combination of subroutines) that copies some data from a spreadsheet into Access.
I then want to be able to run a report based on that data, save it as a PDF and email it out: all automatically.
I don't want the user to have to actually open Access and run the reports, because it's much slower! For some reason Excel can open access, write to tables and close it within a matter of seconds: opening the program manually, editing the table and closing it can take minutes.
I am trying to add a button on my spreadsheet that will give the person Access to one cell only. The cell in its normal state is Locked. The cell is ALWAYS in Column F of a sheet titled Master. The button is located on the sheet titled Master.
The real problem may arise in that once the person has access to the cell and they make their changes I need that cell to return to being Locked and the sheet once again Protected with Filtering.
Does this make sense to anyone? Can it be done and if so any suggestions?
I've trailed through the search engine on here and I can't find an answer to this:
Can you access lookup tables in Excel from VBA code? I assume you can, but can't seem to find anything on this
Adding some additional code to prevent someone from saving the workbook under a different name. Currently if someone tried to enter data after the allotted time period, it would allow them to in any "unlocked" cells. When exiting or trying to save the file with this new data, they will get a message stating something to the effect that this is a read only file would you like to save as another name, which would allow the user to circumvent what I'm trying to accomplish. The end result would be they'd need a new spreadsheet.
[Code].....
Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color?
More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?
I am having difficulties with my Worksheet_Activate() macro. It works great within workbook1 when it is only workbook1 open - but when I open another workbook2, the macro stills runs, presumably because Sheet1 of workbook1 is still activated as well as the newly activated sheet in workbook2.
Is there a way to ensure that only 1 worksheet of 1 workbook is activated at a time? Or that sheet1 of workbook1 is deactivated when workbook2 is opened/clicked on? I need my Worksheet_Deactivate macro to run to get rid of my Worksheet_Activate macro (which runs an application that resets the function of keyboards keys). Otherwise moving around workbook2 is a nightmare. When I navigate back to workbook1 while workbook2 is still open, I still want sheet1 of workbook1 to be activated and my macro to run .
I have the following code that Ger Plante very kindly helped me with which, depending on whether there is an 'x' by someone's name in a list, creates a new workbook, copies some information to it and saves it before moving on to the next 'x'. Loop Through Rows & Copy Each Row To New Workbook
For lLoop = 2 To 251 'first row of data to last row.
If ws1.Cells(lLoop, 4).Value = "x" Then '4 = Column D
ws1.Activate
ws1.Range("e" & lLoop & ":g" & lLoop).Copy
ws1.Range("B1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Rng1.Copy
Workbooks.Add
ActiveSheet.Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Range("A1").Select
ActiveSheet.SaveAs varPath & "Student Data Files" & ActiveSheet.Range("B1") & ".xls"
ActiveWorkbook.Close
Else
End If
Next lLoop
how I can modify the code such that any cells in the range "b1:b504" in Sheet1 of the the new workbook can't be selected or edited without a password....I have tried unsuccessfully using Protect but am not sure how to get vba to set it to specific cells and determine exactly what is allowed in those cells.
Having trouble opening an access file using this code. Using the following code
View 3 Replies View RelatedI looking for a solution to change a Access Query Criteria using a VB code in Excel, without opening the Access DB and changing the criteria manualy?
My normal Job is it:
1) I Work all the time in Excel
2) At the end of the work in Excel, I need to open Access
3) Open a query (Called "MyQuery")
4) Change one parameter the Date: Between 01/10/2008 and 30/10/2008 (This for each month)
5) Run the query
I looking to get rid of points 2) to 5) by replacing with a Command button on an Excel sheet
Sub Change_Criteria_And_Run_Query()
Dim mydbase As Object
Set mydbase = CreateObject("Access.Application")
mydbase.OpenCurrentDatabase ("C:My doucmentsDB1.mdb")
mybase."MyQuery"."Date Criteria" = Between (Worksheet("Sheet1").Range("A1").value) and (Worksheet("Sheet1").Range("A2").value)
>>>>>The above line of code I have problems to get right!
mydbase.DoCmd.RunMacro "MyQuery"
End Sub
I have two procedures in two different access forms. First procedure executes fine. It ends up with creating a Table1 by importing the data successfully from the Excel file. But after that later when I call procedure 2, it throws the below error when it is trying to import the data in to Table2. I think the Excel "Test.xls" did not save properly and close in the Procedure 1. I could see some Excel thread running in the Task Manager. How can I handle this. Please advice. Your expertise help will be truly appreciated. I am absolutely new to this VB-Access code world.
Error: "'MySheet2$'is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long." ....
The database I'm working with uses a lot of back end code to take Access tables and format and output them to Excel Files. After the table (FinalLoadCharttoExcel) has been created through the append query AppndFinalLCToExcel, I need to sort the table by the following fields: "Terminal Number," "State," "3 Digit Zip" and "Begin Zip" all in ascending order. I need to do this within the VB code. I wish I knew a way to get a screenshot of the table, but this is what it looks like unsorted:
Terminal Number State 3 Digit Zip Begin Zip
371 MA 011 78
371 MA 011 00
303 LA 701 06
303 FL 328 31
381 MO 716 04
After all the programs run, I need the table to look like
Terminal Number State 3 Digit Zip Begin Zip
303 FL 328 31
303 LA 701 04
371 MA 011 00
371 MA 011 78
381 MO 716 04
the code always opens a new instance of Outlook even though one may already be open. If left unchecked I may have like 30 instances open if I leave the computer for a few hours. So I was wondering is there some snippet of code I can add in here to check if excel is open first and if so do not open a new instance?
Code:
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
With OutMail
.To = "xxxx@live.com"
[code].....
The following block of code is evaluating the term in the array, if it exists and the value in col. M is a non integer, (which what I am testing for are fractions) then format the cell to a fraction number format.
That is ok, except it is changing the numberformat on text terms. I want it only to change the numberformat on numeric values.
I added the function Isnumeric to this line:
Isnumeric(cells(i, "M").value) Int(cells(i, "M").value)
which I thought would only evaluate numeric cells only, but this was unsuccessful.
Original Code: Unedited.
For i = 4 To LRowf
For Each Item In Array("HAT", "FTWR", "BOOT", "BOOTG", "BOOTY", "HWRISR", "HWBLTS")
On Error Resume Next
If (Cells(i, "F").Value = Item Or Cells(i, "G").Value = Item) And _
Cells(i, "M").Value Int(Cells(i, "M").Value) Then
Cells(i, "M").NumberFormat = "# ?/?"
On Error GoTo 0
Exit For
'End If
End If
Next Item
Next i
The code itself functions as desired however i am getting the vba message "code execution has been interupted". However if i comment out the line
ActiveSheet. Range("Y6:BC6").Delete Shift:=xlUp. Then all is well - so i'd say that is the culprit.
Sub Test()
If Left(ActiveSheet.Name, 5) = "query" Then
Dim intRow As Integer
intRow = ActiveSheet.Range("Y4").End(xlDown).Offset(1, 0).Row
If intRow > 25 Then
ActiveSheet.Range("Y6:BC6").Delete Shift:=xlUp
intRow = intRow - 1
Else
End If
' Runner 1
ActiveSheet.Range("Y" & intRow).Value = ActiveSheet.Range("A5").Value
ActiveSheet.Range("Z" & intRow).Value = ActiveSheet.Range("M4").Value...................
I have recently used a before_close event on this workbook to save a backup of the open file to another location on my system. This works fine but I was wondering if there was some more code I could add to only execute this event on a write access basis.
The file I use can be viewed by anyone on the network as read-only and only certain users with a password can edit/update with a write access password.
The backup event is use executes every time the document is closed be it read-only or write-access.
Ideally I would like to add some code to only execute this backup if the file is opened on a write-access basis.
This code works fine when I run it in Excel.
Code:
Sub RemoveCodeAndSave()
'Remove all code from ThisWorkbook code module
ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule.DeleteLines 1, _
[Code]....
I get the error "Run-time error 440: The specified dimension is not valid for the current chart type" why I am getting this. The macro does everything I want it to, except for throwing the error at the end.
I would like to develop a user-friendly Excel 2010 spreadsheet that would allow the user to enter from 1 to 10 (i just picked this number at random just to have an upper limit) employee numbers in cells A1-A10 and "click" on the VBA Code button that would invoke an Access Query and in the process return all the applicable data gathered from the query back into the Excel Spreadsheet. Believe it or not, I can do all this mentioned so far. BUT what i cant do is my second option for the user which is allow them to pull ALL of the employees back into the spreadsheet using the same query.
In summary, using Excel as a frontend dashboard, I know how to get a specific number of employees' information from a query and I know how to get all employees' information from a query but I dont know how to get either/or. In other words, I could do this with two queries and two "VBA-Code" buttons but I would like to do it with one query and one button.