I want to put the vba code into each "ThisWorkbook" in each workbook that is in a folder. I want the code that is placed in each "ThisWorkbook" to run whenever that workbook is opened.
How would I go about doing that? I have this code so far to open the workbooks that are in the folder.
The amazing code I saw was from this thread Auto lock cells after data entry when file saved... where the code was made by Jafaar Tribak.
It basically locks cells that have had data inputted into them when the file is saved (with a nice prompt warning of the used cells being locked.
This is Jafaar's file [URL]
And this is my file where I'm trying to make it work in the "Employee data entry" Sheet:
[URL]
And finally this is the file after I tried to copy the code over : [URL]
Trying to make it so that in the employee data entry sheet locks cells after an employee inputs some data into said cells.
I thought that by setting an "inputrange" on the employee data entry sheet the code would track it down automatically but maybe I'm completely wrong. I cant see anything wrong with it (probably because I"m not that good at vba) but I fear I may have left out something really simple!
I seem to disable my other macros when i try to insert this code in (I have a macro that when you click on a cell a calendar pops up so you can click on a day from the calendar rather than typing out the date)
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
View 2 Replies View RelatedI have a worksheet named mylist, that I delete, then recreate everytime I run a specific macro. However, I have code in this worksheet that I require, so is there a way for me to insert code to "insert code" on the "re-creation" of the worksheet?
View 9 Replies View RelatedMy company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
Cancel = True
[Code].....
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
[code]....
i need to insert a raw after a certain code like "TRUE" is the code which is mentioned in column A at different intervals , after that i need a blank raw.
EXAMPLE
A B C D E F G H I J K L
1 TRUE
2
3TRUE
4
5
6
7
8
9 TRUE
10
I have a huge data (approx 45000 lines) items .
I have a code that needs to be placed on any new worksheets created. All new worksheets are made using VBA code already so they just need to be told to add the code into each new sheet. How do I do this?
View 2 Replies View RelatedHow is code or functions kept unique to a workbook? for instance i create a few toolbars that are relevant to "work book 1", however when i open another workbook "2" the toolbars do not function properly or are removed by the opening/closing of the work book, similarly other macros seem to struggle with more than one workbbok open at a time.
View 5 Replies View RelatedI am trying to insert via a command button a new row in 2 different tables but I am finding some difficulties . I have attached an excel sheet as example.
View 3 Replies View RelatedI need a simple VB Code which will check the vale in cell A11 in a closed workbook.
If A11=Lunch insert a blank row otherwise do nothing
I'm trying to find some simple code that would that would allow me to select a picture from the picture dialog and insert it to active cell at a small size, like 2 1/2 by 2 inches
View 4 Replies View RelatedI have the code below and it runs a report for me but it puts the new tab to the left of my existing tabs. How can I alter the code to put the new tabs on the right?
If Me.cmbCat.ListIndex > -1 Then
'Add a new worksheet
Set WSReport = Worksheets.Add(before:=Worksheets(1))
With WSReport
'Check sheetname and keep adding 1 until not found.
If SheetExists("Report " & Me.cmbCat) Then
Cnt = 1
Do
[code].....
I am doing up a spreadsheet for work. I have a master sheet which has important 'feeder' info on it. I also have 6 other sheets where the code will reference.
Here is the Data Entry sheet setup.
ColK --> Contains numbers starting in row 2 and ending in row 116 atm, could expand but no further than 500
ColL --> Same as above but with different numbers
ColM --> contains formulas which produce the sheet name that the data in ColK and ColL is related to (6 sheets)
----------
I require some code that will look at each row from 2 to 500 in ColM for something other than "out of range" or nothing (the "out of range" or empty ones should be ignored), and for each relevant row it should identify the sheet name in ColM and then identify the cell that is at the cross reference of the 2 numbers from ColK and ColL and then insert a picture at that cell. Each sheet (the 6 of them) has numbers in ColA from A2 and Row1 from B1. The numbers will exact match those from ColK and ColL from the Data Entry Sheet.
Event type Houring rate Hours worked Total amt to be paid
A 30 2 XXX
B 10 1
C 20 4
Using this example, i wish to add a formula using VBA in cell XXX. Hence, the formula in EXCEL in cell xxx would show =b2*c2+b3*c3+b4*c4.
Is it possible to use VBA to call an existing Excel function? I want to have code that calls up the "Insert - Object" popup window in the same manner as it would if the user clicked the toolbar.
View 2 Replies View Relatedis there a non VBA way to insert the workbook name in a cell?
View 1 Replies View RelatedIt shouldn't be this hard, from within an excel macro I am currently adding a new workbook and doing "some stuff" basically creating a report based on the first workbook.
In the new workbook I want to add a BeforePrint event to the ThisWorkBook Module. I can add it to other sheets, to code modules and other code to the thisworkbook module....but not the BeforePrint?
here is the code I've been working with, I also checked Chip Pearson's site and still can not figure it out.
Add Beforeprint To New Workbook Crashes?
I need to change the colour of cells depending on contents. The following code works perfectly until I try to insert or delete a row in the worksheet. Then I get Run-time error 13, Type mismatch.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Set I = Intersect(Target, Range("E5:E100"))
If Not I Is Nothing Then
[Code]....
I got a code in internet that highlight a cell with equal value after double clicking a cell.
But my problem is I want to clear highlighted cell after clicking another cell (making double clicked cell inactive).
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim c As Range
Dim FirstAddress As String
Dim wksh As Worksheet
Static ColIdx
Cancel = True
[Code] ..........
I try to put a formula to a certain cell using this code, but I get application defined or object defined error:
HTML Selection.Cells(1).Offset(0, 5).Formula = "=INDEX(range1;MATCH(""D""&C13;range2;0);MATCH(""S""&D13;range3;0))"
The weird thing is, that if I type the same formula itself to a cell, it works, so the problem shouldn't be with the formula:
HTML =INDEX(range1;MATCH("D"&C13;range2;0);MATCH("S"&D13;range3;0))
On the other hand I tried to replace the formula in my VBA code with simpler one, like this one and that worked aswell:
HTML Selection.Cells(1).Offset(0, 5).Formula = "=a1+a2"
I need to use sendkeys to insert code into codepane in VBE.
I use sendkeys "MyFunction()", but the "()" doesn't show up just "MyFunction"
I use sendkeys "MyFunction" & chr(40) & chr(41), still only get "MyFunction"
I'm using the below to code to insert a formula in cell E1, what am i doing wrong here.
View 5 Replies View RelatedCan VBA code that inserts between written rows a certain number of empty rows (and to be able to specify somewhere in the code the number of rows to be inserted)
View 9 Replies View RelatedI have a requirement where I have to add a row with x columns using vba. this is the code I am using now
k = 17
prodetails = .GetFieldValue("Product Details")
sSeats = .GetFieldValue("Seats")
If isRecord Then
While isRecord
Sheets("Products").Rows(k).Insert Shift:=xlDown
Sheets("Products").Rows(k + 1).Insert Shift:=xlDown
Sheets("Products").Rows(k + 2).Insert Shift:=xlDown
ThisWorkbook.Sheets("Products"). Cells(k, Prod6Col) = prodetails
ThisWorkbook.Sheets("Products").Cells(k, Unit4Col) = sSeats
isRecord = .NextRecord()
Wend
End If
In this code I have declared Prod6Col and Unit4Col as constants. instead I need to use them as variables like in this modified code. However if i use this code, I get an object definition error.
k = 17
m = 20 (20th column)
l = 18 (18th column)
prodetails = .GetFieldValue("Product Details")
sSeats = .GetFieldValue("Seats")
If isRecord Then
While isRecord
I am trying to insert 50 tabs (at different points in time during a macro) that each has a state abbreviation. I know how to get the macro to add a tab, but to name it, it wants to select " Sheet 4". The problem with this is, the tab that was created might be sheet 15 or sheet 1, and I need it to be able to name the tab no matter what sheet # it is.
View 7 Replies View Relatedif its possible to insert VB code into an open worksheet using VB code from another workbook.
I have a script which is cycling through workbooks in a folder inserting formulas into cells etc as there are so many. I would also like to add in some VB code to these work books at the same time so as to avoid doing it manually.
How do I insert an existing worksheet (previously exported from the same workbook)? I need to be able to do it programmatically.
i have a workbook with over 100 odd worksheets of different names... i want to insert 1+ columns in every worksheet . For Example ...
in worksheet 1 name abc .. insert 1 column in column B (put clumn b data in column C)
in worksheet 2 name def .. insert 2 column in column B (put clumn b data in column d)
in worksheet 3 name ghi .. insert 3column in column B (put clumn b data in column e)
how to insert multiple tabs in the workbook?
i try this
Code:
Option Explicit
Sub Add_Multiple_Tabs()
Dim Tabs As Variant
Dim I As Byte
[Code]...