This snippet of code works fine when I run it. When another user uses this same workbook and runs it it gives an error:
Sheets("Data").Select
Range("A2").Select
ActiveSheet.Paste
This is the line highlighted by excel:
ActiveSheet.Paste
I have tried changing it to:
Sheets("Data").Paste
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 RelatedHow do you add a macro to a cell that works wen the user tabs out the cell?
View 9 Replies View RelatedVery slow small vba code. I have the following
View 2 Replies View RelatedThe following code is a is designed to paste into "Y1", the value in column 5 of the active row when that cell is changed. This works perfectly if I select the value from a dropdown list and hit enter, or if I enter a value manually and hit the right or left arrow key. However if I manually change the value of the cell and then hit enter, it does not work because it looks for the value in the next row.
Does anyone have any idea what I can do to make the macro stay focused on the cell who's value changed even if I hit enter?
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim AR As Long 'Active Row
AR = ActiveCell.Row
If Target.Column = 5 Then
ThisRow = Target.Row
If ActiveCell.Value > 0 Then
Selection.Copy
This code, supplied through this forum, works perfectly in one workbook but not another. I have created a range called ColourRange, one called ColourIndex but I am getting a '400' error message when I attempt to run it. Can anone explain to me (in very simple terms) why it won't work?
Sub CopyFormatMMT()
'Colour code Owners
Dim r As Range
Dim f As Range
Dim c As Range
Dim j As Range
Set r = Range("ColourRangeMMT")
Set f = Range("ColourIndex")
Range("ColourRangeMMT").Select
Selection.Interior.ColorIndex = xlNone
For Each c In r.Cells
For Each j In f.Cells
If c = j Then
c.Interior.ColorIndex = j.Interior.ColorIndex
End If
Next j
Next c
Range("C9").Select
End Sub
I am trying to programmatically select an item from a single selection listbox (i.e., set to fmMultiSelectSingle). The following code works properly only every other time I run it; the problem is extremely repeatable.
Const storeStartCell = "B5000"
Private Sub ListBox_CounterTOs_Click()
selectedTOName = CStr(Mid(Worksheets("Sheet1"). Range(storeStartCell).Offset(ListBox_CounterTOs.ListIndex, 0), 3, 3))
selectedTONumber = CInt(Mid(Worksheets("Sheet1").Range(storeStartCell).Offset(ListBox_CounterTOs.ListIndex, 0), 6, 4))
ListBox_TO_Name.Value = selectedTOName
ListBox_TO_Number.Value = selectedTONumber
End Sub
The times that it works, ListBox_TO_Name.Value is set to something like "ABC" and ListBox_TO_Number.Value is set to something like "1234". When the values get set properly, the associated click subroutines (e.g., ListBox_TO_Name_Click()) get called automatically when the value is set. The times that it doesn't work, they are both set to "" and the click subroutines do not get called. I also tried
myListbox.Selected(myIndex) = True
I am trying to create this macro for my accounting journal What I want to happen is that in my sheet1 if the 1st cell in column a is "CASH" then the whole row should be copied and pasted in sheet 2. i want this to happen from the first cell in a column until the very last data in column a which means i am not certain up to what row number it will have data since this is a journal with uncertain number of transactions.
View 9 Replies View RelatedI wrote code to update some workbooks. The code opens the workbooks and then activates the workbook to add the update.
I was tired when I wrote the code to activate the workbook and it is written:
Workbooks("Update").Activate
The updates have already been sent out and it is not working on some computers. (If I change the code to
Workbooks("Update.xls").Activate
it works fine.)
Is there some option in the VBA editor that I can have people change on their computer so the code will run? Why does it work on some computers and not others?
(Unfortunately, rewriting the code to add ".xls" and resending everything isn't an option.)
I put together about 10 separate macros that will log you into a site using the values in a given cell. I was having trouble with the last one because of AutoComplete remembering the username. So I put in an IF statement, and when I used F8 to go line for line, it works perfectly. However, when I click F5 and just let it play through, it doesn't log in. I tried adding a 5 second delay, but that didn't seem to work. I still get a run-time error when I hit play.
It get hung on this line:
Code:
If doc.getElementById("user_name").Value = cUsername Then
But the complete code is this:
Code:
Sub StreetLinks_Login()
'On Error Resume Next
Dim cURL As String
cURL = Worksheets("Sheet1").Range("B9").Value
[Code] ..........
I am trying to write a function that searches a column for an index then sums the value in a corresponding column. This function works when run in the immediate window of VBA but when I use it in the spreadsheet it can't find any rows.
The reason I need this formula is because there are more than one rows that can be found.
Function SumIndex2(ByVal sField As String, ByVal sIndex As String) As Double
Dim i As Integer
Dim dSum As Double
Dim rng As Range
Dim firstaddress As String
I wrote the following basic code earlier (which will end up being part of a larger code)
Code:
Dim DateInput As String
Dim LastCell As String
Sub LastRowInputBox()
LastCell = InputBox("What is the last row number in the range?", "Last Row Input")
[Code] ......
As you can see, all the ColumnCopyInsert Subroutine does is copy select/copy four columns (E:H), inserts the copied selection immediately to the right, inserts formulas and autofills down to the last row required. I added the LastRowInputBox routine as the range of rows varies from week to week (inserting new rows, deleting others).
The code works great...up to a point unfortunately. If ran as it is, everything that should be copied/pasted will be and cells autofilled to the row specified via the InputBox. Here comes the problem, if any rows are either inserted or deleted then the code throws out "The object invoked has disconnected from its clients" when it reaches the first instance of the following line:
Code:
Selection.Insert Shift:=xlToRight
At which point, Excel hangs (or maybe in some sort of loop) and I have to open Device Manager and close the Excel exe process.
I have searched the FAQ's but have not found a suitable answer to my problem. I have some code that works perfectly when it is run from the VB Editor but when I put it behind a command button it gives me an error almost straight away. I have read that when a command button is used the command button defaults the active sheet to the one that it is one therefore you always have to specify the active sheet but I have done this so am still confused as to why it is falling over. Below is my code, I have commented where it is tripping:
Sub FormattingAcutalReport()
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesMF Consolidated Risk DAILY LIVE DATA FROM BO.xls"
Workbooks.Open Filename:="H:Risk ReportingDaily TemplatesDaily Non Banks LIVE.xls"
Dim myBorders() As Variant, item As Variant
Set SEGNSEG = Workbooks("Todays Reports.xls").Worksheets("Seg and Non Seg Bank Summary")............................
I have this code attached to a button on the first sheet of a workbook with hundreds of sheets.
it is suposed to look for a cell that contains "SAY:" and then move one column to the right and make it a zero. It works on the first sheet but not on any other sheet.
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.
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
I'm playing with simple UserForms to get the hang of VBA. I was trying an example for the help files (Using xl2K), and this isn't working. My first challenge was figuring out that I needed to 'create' a UserForm to begin with.
View 2 Replies View RelatedI have a code to verify the user name and password, what I want is counting the times the user enters wrong password and show a msgbox when he enters three continuous wrong passwords:
I have created the following lines which are part of a long code, the code is actually running without errors, but it is not showing the msgbox,
how to protect source code from user or give the password when the user view code, right click on a sheet.
View 4 Replies View RelatedI have a workbook that is shared between 10 users and this work book is known as Master, what I don't want to happen is for one of the users to over write and save it. So is there a way one not allowing the user to save but allow the user to save as.
View 2 Replies View RelatedI have a situation where I have an msg box pop up that asks the user to identify whether or not the file they have chosen meets the standards needed to continue running the macro. Specifically, I need them to answer "Yes" if the file has a list of data in the first column, if there are no blanks in that list and if the first row of data begins on Row 4. If they answer yes, the macro continues ahead and does what it needs to. If the person selects "no," another input box pops up that asks the user to make the edits. This msg box asks the user to click "ok" when they have made those edits. I want the macro to pause after the person has selected "no" from the first msg box, so that the person can make those corrections. Then, I want the macro to resume when the user selects "OK" on the second msg box. How might I do this? I have included the piece of my code below.
strresponse2 = MsgBox("Please confirm that the file you have selected meets the following standards:" & vbNewLine & "1. The information in the first column of this file is all of the job titles or job codes associated with this profile." & vbNewLine & "2. From the first job code or title to the last, there are no blank rows in this first column of data." & vbNewLine & "3. The first job title or code appears on Row 4, Column 1." & vbNewLine & "If the file you selected meets these standards, select Yes. If the file you selected does not meet these requirements, select No.", vbYesNo, "Yes/No")
If strresponse = 6 Then
Call getjobtitle
End If
If strresponse = 7 Then
'pause macro
strresponse2 = MsgBox("Please make the necessary edits to this file. When you are done, select OK to continue generating your job profile.", vbOKOnly, "OK")
If strresponse = 0 Then
'resume macro
Call getjobtitle
End If
End If
I have created a custome User Form with three option buttons inside a frame. How do I set the value for each button and then how do I pass that value back to the code that opened the form (after closing the form upon a selection?
Sub Test2()
UserForm1.Show
Select Case List
Case 1
Range("b1").Value = "List 1"
Case 2
Range("B1").Value = "List 2"
Case 3
Range("B1").Value = "List 3"
End Select
End Sub
in the form I have the following code associated with each of the butons (but I do not think I have it right). Isn't there a way (becasue I have them in a frame) to shorten the code?.................
I have these codes in place in my excel file, there is nothing wrong with them at all. Here is the the problem : My superior runs the macros in place in the file almost daily, and when he is done he then closes it and all is well.
The macros does things to other files so they are changed in a way that the supervisors like them. I avoided putting macros in these individual files, because of the attempt to make it as simplistic as possible without them being able to affect overall file.
The problem happens when he (my superior) accidently makes a change in the primary sheet in the workbook with all the macros, it affects alot of my formulas and macros that are already in place. Within this workbook, it keeps a log of things from the other files results, so I have to have a record of it in the workbook, hence having a save event in place.
The Question: Is it possible to create a macro button, that will allow him to disable the codes below, so that he can close it and open again, so that it doesn't show any of the errors that happens by accident and try to close it again so there is no errors? Basically, I am asking what is the code for this, and where would I place it in.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mylogoff
Set mylogoff = Sheet1. Range("A65536").End(xlUp).Offset(0, 1)
mylogoff.FormulaR1C1 = "=NOW()"
mylogoff.Copy
mylogoff.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = Fals
mylogoff.Offset(0, 1).FormulaR1C1 = "=RC[-1]-RC[-2]"
Application.DisplayAlerts = False
Sheets("Start Here").Select
ThisWorkbook.Save
ThisWorkbook.Close
End Sub......................
I'm trying to write a macro that will bring up the "File Open" dialog box, and allow the user to navigate to the directory where the file is located. It won't be the same file every time, and the files will likely have different names.
View 14 Replies View Relatedfind a VBA Code for fliping images in a User Form. What I want is that, I have a user form in which I have a command button "Next" & "Previous". I want whenever I click "Next" button my image control should show the next image & whenever I click "Previous" button my image control should show the previous image. I tried many logics but failed. Might be you guys will crack this hard nut.
View 7 Replies View RelatedI really don't know how to code a checkbox correctly. On the sheet, I have a form with CheckBox1 and Checkbox2. I would like Checkbox2 disabled until Checkbox1 is checked. Also, when Checkbox1 is checked, display Image1, otherwise Image1 is hidden. When CheckBox2 is enabled and checked, display Image2, otherwise Image2 is hidden. I know this is real beginner stuff, I'm still a rookie.
View 4 Replies View RelatedI have created code to enter data from the Active cell. However, I would like to edit it so that the user can only make this work if they are on Rows 6 through Row 29.
Here is the
Sub DropDown7_Change()
'Right here I want: If ActiveCell. is in Row 6-29 Then
Application.ActiveCell.Value = Range("D3").Value
Application.ActiveCell.Offset(, 1).Value = Range("F3").Value
Else Msg = "Move into the proper rows"
End Sub
How can I do a check before the code is executed to be sure the users active cell is in Rows 6-29.
Need way to interupt my code whilst the user selects a row. ie. if I wanted to move the contents of one row to another I would want to say: "select source row" (and want the user to click on any cell in that row) I could then put the rownumber in a variable with
View 2 Replies View RelatedIs 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 need the code to allow a user to select a column of their choice in an open workbook of their choice so that data can be lookuped up and changed. I am happy to create the code to do the lookup/changing, however the nearest I have found is the use of an inputbox which is limited to the active spreadsheet only. Some people have said the input box could be used over to another spreadsheet but I have not found that to work.
What I will need returned is the workbook name, sheet name and column. That will allow me to point to that and do the task.
This code could be used by 5 people and those 5 people could be running it on 20 different spreadsheets.
I thought this would be easy to find but it has eluded me. When using Excel normally, if you do a simple lookup formula, or create pivot function, excel prompts the user to select the workbook/sheet.