Ambiguous Name. Can A VBA Combine 2 Macros

Nov 15, 2006

I've got two macros that work beautifully apart from one another ... but put the two in the same Excel workbook and they bump heads. I get the infamous "Ambiguous name detected: Worksheet_Change"

I know enough about VB to get myself into trouble. So, I'm asking for help from a talented person to show me how to combine the two ... or another work around to get through this.

Here are the two sets of code in my sheet that I think are causing the ambiguous name problem. How can I work around this error?


Private Sub Worksheet_Change(ByVal Target As Range)
Dim varFind As Variant
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column 2 And Target.Column 9 Then Exit Sub
If IsEmpty(Target) Then Target.Interior.ColorIndex = 0

If Target.Column = 2 Then

Set varFind = Columns(16).Find(What:=Target.Value, LookIn:=xlFormulas, LookAt:=xlWhole)
If varFind Is Nothing Then
Target.Interior.ColorIndex = 0
Else
Target.Interior.ColorIndex = Cells(varFind.Row, 16).Interior.ColorIndex
End If
End If

ADVERTISEMENT

How To Combine 2 Macros

Mar 14, 2009

I probably have a odd request, but then I think I'm just different ?

I have an Excel Workbook with Col. A approximately 1/8" wide. The formatting of this col is that the cells are set to have the typing fit into the cell, therefore it becomes so small that no one can read it. Just what I wanted. If the user needs to read what's in the cell, they can click on the cell and read it in the formula bar.

Col. B is approximately 2" wide

1st, the user enters the Path and File name, with extension, into cell A34(example).

2nd, the user types in a File Name that they will best understand into cell B34.

The Workbook is now saved.

When the user wants to open that file, they click on the Name . . . Col B34 - Easily recognized by the user.

They then click either the Button that say's "Open Excel File" -or- "Open Word File.

The macro automatically moves the selection over to column A, then the file is opened.

Now what I need

I want to use just 1 button and I will name it "OPEN"

The 2 below macros work excellant and I still need the functionality of each. What I want is that 1st it trys to see if it is an excel file and if so, opens the file and stops.

If the macro determines that it is not an Excel file, the macro then goes to the Word macro and determines if it really is a Word file and if so, opens the file and stops.

NOTE: I want to point out that I still need all the checking etc that the 2 macros currently do.

Here are the macros

OPEN EXCEL FILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

View 10 Replies View Related

Combine 2 Macros Into One

Jun 16, 2007

I have 2 macros that work as I want them to. First one gets ride of formulas and formatting etc. and then deletes itself.

Sub Quote_Wrapup()
Range("A18:A1018").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E").Select
Range("A980").Activate
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B1021").Select
ActiveSheet.Shapes("Picture 14").Select
Application.CutCopyMode = False..................

I don't really understand how to combine them so that they will work as one.

View 3 Replies View Related

Combine 2 Working Macros Into One?

Aug 3, 2014

I now have 2 working macros but cant seem to combine them correctly. How do I get them both into one macro so they run together.

View 2 Replies View Related

Combine 5 Macros In A Sequence Into 1 Macro?

Nov 28, 2012

I have 5 macros to be run in sequence. I have copied them into the file attached with ----- line as separator between them.

How can I possibly combine them all into ONE MACRO?

View 5 Replies View Related

Combine Two Macros For Dropdown Boxes

Feb 25, 2014

I have a spreadsheet I use to track reprimands for our employees. Reprimand Counseling Spreadsheet Example.xlsm. Previously, I had to read the entire reprimand in order to determine the policy violation(s) and then manually enter those reasons into the cell. We have a new standardized form, where the manger selects the reason(s) for the reprimand. I wanted a drop down box that I could select multiple reasons for the reprimand and have all the reasons appear on the same line, as well as being able to remove a reason from the line by selecting it again (so I wouldn't have to start over, in case I selected the wrong reason). I was able to find this code, on [URL] .... to accomplish that.

Same Cell Add & Remove

I also need to be able to manual edit some of the reasons. For example "Missed Meal Period(s) #___". I will need to be able to edit that item to read "Missed Meal Period(s) #2". I was able to find this code, on the same website as the previous code, to accomplish that. However, this code includes a line break, which I do not want.

Edit Same Cell

[Code] .....

Since I need the code to work on each worksheet (CIVI - SOCR), will I have to enter the code on each worksheet or is there a better way to accomplish this? Is there a way to combine these two codes, while simultaneously removing the line break? I need to select multiple reprimand reasons in the same cell and edit the reasons, if necessary. I've read and reread that article for hours, but I can't get it to work.

View 14 Replies View Related

Combine Two Macros And Run Them On Double Clicking A Cell

Mar 20, 2009

I have two pieces of code. The first one puts a tick mark in the cell when you double click it. The second one hides column B of another sheet if the value in cell A1 of Sheet1 is "a". I want to combine these two codes so that when I double click cell A1 of sheet1 it puts a tick mark in the cell and hides column B of Sheet2 and clears the contents of range B2:B50 of Sheet2.

View 4 Replies View Related

Combine Two Macros In Same Worksheet Change Event

Oct 1, 2013

combine two Worksheet Change event macros into the one macro? The macros are listed below.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long

[Code]...

exitHandler:

Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("O:O")) Is Nothing Then
Application.EnableEvents = False

[Code]...

View 7 Replies View Related

Ambiguous Name Detected ..

Nov 22, 2006

I need to use both of these VBA scripts in my excel project. The first is this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Sheets("Sheet2").Range(ActiveCell.Address).Value = 1 Then
ActiveCell.Interior.ColorIndex = 3
End If
End Sub

The second is this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lScrollRow As Long
Dim lScrollColumn As Long
Dim lTargetRow As Long
Dim lTargetColumn As Long
Application. ScreenUpdating = False
lScrollRow = ActiveWindow.ScrollRow
lScrollColumn = ActiveWindow.ScrollColumn..................

When I enter both into the script box and then try to run it, it says that there is a compile error with an "ambiguous name detected "worksheet_selectionchange"

View 4 Replies View Related

Ambiguous Name And Naming Of Sub Routines

Apr 6, 2009

I'm running a sheet with three pieces of code (all blagged from these forums thank you) Two of the sub procedures have the same name "worksheet_change which generate an ambiguous name error. This I sort of understand. However I've tried changing the second one of the subs names to "worksheet_change1" and whilst the ambiguous name error goes away the sub routine then fails to function.

View 2 Replies View Related

Ambiguous Name Detected: FormatCell

Sep 4, 2009

my macros to search for the word "Cancel" in columns "T". Once found, I need the macros to make that entire row a certain pattern ( .Pattern = xlGray16)

There will be other wording in these cells that contain "Cancel" or "Cancelled".

A compile error keeps occurring. It says "Ambiguous name detected: FormatCell"..

View 14 Replies View Related

Progress Bar :: Ambiguous Name Detected

Nov 2, 2006

I want to include a progress bar in my macro and have got the code from another website. However, after the progress bar is displayed i cannot get it to move to the next line of code in the module. In the example i've got, the code in the user form window simply calls a sub called "main" and continues on through that sub. Whenever i call one of my sub routines an error box states" ambiguous name detected". This never happens with the sub called "main"? Why is this happening? A portion of the code is shown below. After "userform1.show", the user form is displayed but the program will not move on to "get_blades" sub routine?

Browse_Folder
' Sub routine "browse_folder" selected to select folder
' containing the mea files.

UserForm1.LabelProgress.Width = 0
UserForm1.Show

' HourGlass

get_blades (lookinname)
' Use sub routine get_blades

Range("T3").Select
sort_data

View 7 Replies View Related

Ambiguous Name Detected With Constant

Oct 24, 2007

Option Explicit
Public Const ARK As String = "Inddata-ARK"
Public Const VVS As String = "Inddata-VVS"
Public Const KON As String = "Inddata-KON"
Public Const EL As String = "Inddata-EL"
Public Const LD As String = "Inddata-LD"
Public Const KL As String = "Inddata-KL"
Public Const OpDat As String = "Opdatering"

And then macro's under it... So the aboce is in module "INIT_S". Now in another module "RUN_P" I have a sub-rutine that uses these constants with a select case.
Likes this:

Sub OpEksInitNu()
Select Case ActiveSheet. Name
Case ARK
With OpEks
'more stuff
Case VVS
'more stuff
Case KON
'more stuff
End Sub

But when I try to run OpEksInitNu i comes with ambigious name with ARK as the error, later down I use another public constant. But i keep getting the ambigious name error.. What could cause that... I have tried to locate all ARK, VVS etc. to try to determine if it is used later on, but it's not! I only declare the constants one time, and no module is called ARK, VVS etc.

View 2 Replies View Related

Ambiguous Name In VBA Is Not Allowing To Open File?

Mar 19, 2014

During a copy and paste I forgot to change the name of a macro in a module which had many macros. Now when I want to open that file , it hangs and does not open. how to open the file to change the name of the macro and re-save it ?

View 5 Replies View Related

Compile Error Ambiguous Name Detected

Feb 16, 2010

Hello, Im using a script that allows me to autocomplete a data validation list using a combo box and the code shown below. However I am getting a compile error ambigous name detected and the following line is highlighted

"Private Sub Worksheet_SelectionChange(ByVal Target As Range)"

I know its because this is used twice but I am not sure what to do in order to fix this.

View 11 Replies View Related

Excel Error - Ambiguous Name Detected

Nov 5, 2013

I need to have this spread sheet date when a cell is changed. I have set up the Visual Basic editor and it works for the column D when updated puts the date in column E.

But I have duplicated what I want for the other columns but I get ambiguous name detected and the other columns do not work my knowledge of macros has hit its limit ...

View 4 Replies View Related

Ambiguous Name Detected Running Macro

Nov 2, 2006

I want to include a progress bar in my macro and have got the code from another website. However, after the progress bar is displayed i cannot get it to move to the next line of code in the module. In the example i've got the code in the user form window simply calls a sub called "main" and continues on through that sub. Whenever i call one of my sub routines an error box states" ambiguous name detected". This never happens with the sub called "main"? Why is this happening?

View 2 Replies View Related

Compile Error: Ambiguous Name Detected: Worksheet_Change

Oct 28, 2008

Receiving the following error:

Compile Error:
Ambiguous name detected: Worksheet_Change

How can I resolve this? Is there a way to combine these two (2) statements?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$O$3" Then
Range("B2:L13").ClearContents
Range("A17:A27").Copy
Sheets("Letter").Range("A16:A26").PasteSpecial xlValues
Application.CutCopyMode = False
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "TextBody" Then
Range("A17:A27").Copy
Sheets("Letter").Range("A16:A26").PasteSpecial xlValues
Application.CutCopyMode = False
End If

End Sub

View 5 Replies View Related

Encourage Enabling Of Macros: Disable Macros When Opening Then The Worksheet Menu Bar And Other Command Bars Are Still Available

Aug 26, 2009

I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.

View 6 Replies View Related

Enable/Disable Macros When No Macros Are Present

Jul 28, 2008

I have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.

I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.

No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?

View 9 Replies View Related

How To Delete Another WorkBook Macros Using Macros

Jul 23, 2005

What is the commands or script for deleting a macro automatically using
another workbook macro.

View 9 Replies View Related

Hiding Macros From The Run Macros List

Jul 14, 2008

Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.

View 9 Replies View Related

Macros Really Slow The Another Macros Is Run

Dec 14, 2009

I have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k

Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...

For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l

Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.

View 11 Replies View Related

Version 4 Macros And Vba Macros In Them

Apr 13, 2007

how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.

View 2 Replies View Related

Combine The Specified Value

Feb 10, 2010

I have 12 workbooks (one for each month), they have a list of assets to our company and a running total of all expenses for the month that they incurred. The 13th workbook is a yearly analysis of each asset. I need to transfer all charges into this worksheet.

So for example:

Worksheet 1 (January)

Column A Column H
Asset # Total Expense
5021 $3,041.52
970289 $242.08


Worksheet 13

Column A Column B Column C Column D
Asset # Asset Description January February
5021 Rivet Machine $3041.52 $345.65

So hundreds more machines for twelve months can take awhile to populate this info and very confusing when dealing with separate worksheets.

View 7 Replies View Related

Combine 5 Procedures Into 1

Apr 16, 2013

I need to get 5 small subs into 1 sub. (run the whole program by pressing once.

Please see attached file with codes : ServiceDriftMaaling.rar

View 6 Replies View Related

How To Combine Three Formulas

Mar 29, 2014

Ok, These are the three formula's. How do I combine them or can I.

=IF((B2="GOV"),[@[Gal Billed]]/1000*1.5)

=IF((B2="NPROFIT"),[@[Gal Billed]]/1000*2,0)

=IF((B2="COM"),[@[Gal Billed]]/1000*3)

View 4 Replies View Related

Four IF Statements To Combine

Jan 29, 2014

I need to write IF statement with 4 criteria, or at least I think IF will do the trick. It would be difficult and not very visual to describe my question here, therefore I have attached a dummy workbook with the comments. Basically I have got 2 values in 2 columns. Next 2 columns will hold letter "x". There are 4 possible variations how "x" will appear in those two columns: first, second, both or none. So depending where the "x" is, I need to return one of the 2 values.

View 6 Replies View Related

Two Formulas Need To Combine As One

Nov 9, 2007

I have two formulas that i need to combine as one, but i dont now how.

This is the other formula:

=SUMPRODUCT((Huollot!I2:I500="Tyn alla")*(Huollot!K2:K500="Warranty")*1)

The other one:

=IF(NETWORKDAYS(Sheet1!C1;Huollot!C2:C500)>20;1;0)

the biggest problem is that this formula does not work as it is. Or is there some other function than networkdays that i could use?

The purpose of the formula is to count how many "objects" that are in service have been there for over 20 days. There a a few other criteria also, but sumproduct takes care of that. Can networkdays even be used the way im trying to?

View 14 Replies View Related

Combine The Cells In A1

Nov 26, 2008

In cell B1 I have the number 1
In cell C1 I have the number 1031000

I know how to combine the cells in A1. I am getting a result of 1-1031000

What I would like to happen is that 1 becomes 001.

Ending result is 001-1031000

problem is that cells in B could be one, two or three digits.

On a small scale I could fix manually but i have over 15,000 rows to deal with

View 2 Replies View Related

Combine Number In One Row

Dec 24, 2008

I want to combine number in one row.

Column A
12
13
14
15

Need to be in column B. 12,13,14,15. I tried =A1&,&??

View 3 Replies View Related

Combine The IF And OR Queries

Jan 8, 2009

Trying to write an IF query looking for multiple returns that would equal the same result.

I have a series of number where the last digit would result in the same return if the digit were 1 or 6, 2 or 7, 3 or 8, etc.

1 or 6 = 1
2 or 7 = 2

I have been trying unsuccessfully to combine the IF and OR queries

View 12 Replies View Related

Combine SUMIF With IF

Jan 23, 2009

I am trying to use SUMIF as part of an IF statement, What I am trying to do is, for example, if a column has two values M and P, then SUMIF only those rows that have a corresponding M, and not calculate the P's.

View 9 Replies View Related

Combine Several Spreadsheets Into One

Jun 24, 2009

I am so frustrated to combine several spreadsheets into all in one, and seperate all in one spreadsheet into several with the same header frenquently. Pls check the attached example excel spreadsheet., could anyone teach me how to program in vba to achieve the result as above?

View 6 Replies View Related

Combine The Cells

Oct 15, 2009

i m working on a spreadsheet and i need cell A+B=C..

Example
Cell A has a first name (John)
Cell B has a last name (Smith)
Cell C has last name then first name seperated with comma

View 4 Replies View Related

Combine Three Cells Into One?

Jan 23, 2013

Is it possible to combine three cells into one? Like for example, column A is the first name, column B is the middle name, column C is last name.

Is it possible put it on a format like this: Last, First M.

(middle name on column B is a name, not an initial.)

And can I make it only the first letters are capped. Last, First M. Or all small letters. last, first m.

View 7 Replies View Related

Combine Two Tables Together?

Apr 1, 2013

I have:

- Table 1 has ~1,600 records (name of game, meta score, user score)
- Table 2 has ~3,000 records (name of game, genre, publisher)

I want:

- Table X has ~1,600 records (name of game, meta score, user score, genre, publisher)

Basically, I want to take the genre and publish columns from Table 2, and add it to Table 1. However, I want to ignore the extra ~1,400 games that Table 2 has.

I'm nearly a complete noob when it comes to excel. Noob to the point where I don't even know what this thing is called, so I don't even know how to search for it properly! I know something can be done since there is a common column between the two (name of game).

Edit: Also, I think it'd be easier if I were able to attach my excel spreadsheets but I don't even know how to do that

View 1 Replies View Related

How To Combine Two Worksheet Into One

May 1, 2013

combine two excel worksheet into one new worksheet. thing is on both of these worksheet doesnt have a unique identifier. Is that possible & how.

View 2 Replies View Related

VBA To Combine Data

Aug 18, 2014

On Sheet1 in Range A1:A10, I have the following data: O O M3 M O O M M D9 O (in reality, the data covers the range K38:AN90, but I'm simplifying my requirement here)

On Sheet2, in Range A1:A10, I have the following data: O O O O A1V O O O O D1V

I need some VBA code that will loop through each cell on Sheet2 and where it comes to any containing the letter 'V', it will replace the data on corresponding cell on Sheet1 with that data.

In this example, after running the code, Sheet1 will read: O O M3 M A1V O M M D9 D1V.

The only other caveat here is that I need to be able to return to the original data on Sheet1 but I'm thinking I'll add some code to toggle back by making a back this up on a separate sheet so I can immediately reinstate original data on Sheet1.

I want to avoid 'Copy/Paste' and if at all possible so prefer to use Range("X").Value = Range("Y").Value etc.

View 2 Replies View Related

Combine Files

Jan 23, 2009

I have a scenario here and i really dont even know where to start. If anyone can help me with some code for this project that would be great.

I have a folder that contains several subfolders (10-20). Within each subfolder is a number of excel files (the number of files varies) that contain 2 worksheets each.

What i am trying to arrive at is one file for each subfolder that has the first tab of each of the files. The original files contain links, so the values and formats must be pasted. It woul dbe great if all of the new files could be saved in the main folder among all of the subfolders.

It would be especially great if each tab could be named for its respective file and each new file for its respective folder.

View 6 Replies View Related

Combine Many Columns To One

Nov 17, 2009

Essentially need a macro to combine the columns into 1 column.

Need a Macro to put everything in each "box" into one column, for example CU is the end of the first box, CV to GP is the second box ect. Essentially a box is the row where the yellow line is to the column where the yellow column is.

The problem is the setup of these colums change, sometimes they are bigger sometimes smaller and the spaces beneth it change. Sometimes I might have a column which goes to row 18 followed by 3 blank rows then the yellow box

I first design the first colum then select that and drag across to complete the rest ( my real file has links ect )

So I need a macro in which it can scan the first column up to the yellow line then put everthing within a "box " into 1 column, each )

then repeat the same process for other boxes with each box being put in its own column ( preferablly leaving an empty column in between )

THe file itself will help clarify any problem, if this request is not possible if it could combine every column into 1 column ( along with the spaces to the yellow line ) that would help also, i could manually cut each box into another column then.

View 10 Replies View Related

ADVERTISEMENT