Linked Cell Combo Box Hangs Running Macro
Dec 1, 2006
I am trying to create a menu that calls macros based on your choice using a drop down combobox (from the control toolbox). Everything works except: When i try to reset the drop down to "Main Menu" using the Linked cell $B$1 excel hangs and suts down evertime. What i would like it to do is reset the drop down to "Main Menu" each time one of the other drop downs are selected. I have really enjoyed this forum and it has been a great resource for me in the past I hope someone can help. I have attaching the file for review.
View 3 Replies
ADVERTISEMENT
Jul 3, 2007
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.
They are four PC's along with our Managers laptop that run this Macro daily.
About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.
We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.
I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.
I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.
View 9 Replies
View Related
Mar 6, 2007
I am trying to create a combo box whereby i need to populate a list of name into the box and when user click on the name, it will linked it to the corresponding letter in MS Word.
Have tried a number of codes available in this forum (to populate the combo box) but were not successful.
View 4 Replies
View Related
Jul 11, 2006
I have written a macro .When i execute the macro the result is displayed but my excel file hangs after that.
View 3 Replies
View Related
Sep 15, 2006
I've managed to use some code I found to add a new row below the selected row, and duplicate all the forumlas of the source row. It worked fine dozens of times yesterday, but today it's decided not to work. It gets as far as creating the new row(s), but then just hangs & excel crashes before duplicating the formulas. I've even tried reverting to an earlier version, which also worked fine, but this crashes also!
Sub Add_New_Row()
' Unlock Worksheet
Worksheets("Sheet1").Unprotect Password:="*****"
Dim x As Long
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line
Dim sht As Worksheet, shts() As String, i As Integer
Redim shts(1 To Worksheets.Application. ActiveWorkbook. _
Windows(1).SelectedSheets.Count)..................................
View 7 Replies
View Related
Oct 29, 2009
We have had a macro running for a few years (Excel 2007 now, but started in 2003) that imports about 35 text files into separate sheets, creates a calculated SUM field for each sheet and copies that value to a title (or summary) sheet. We use this 2 to 4 times per year at inventory time, copying to a new file and deleting the old data before running the macro. The imported files initially create new workbooks, but the data is copied to the initial workbook into a distinct sheets for each file.
Recently (well, last August) this macro started hanging after importing (Workbooks.OpenText) a number of files, and not necessarily the same file every time (on repeated runs.) While trying to figure out the problem, I have now managed to get it to hang every time on the first file! However, if I am stepping through in the debugger it continues past the OpenText command and on F5:Run/Continue will then continue processing the rest of the files normally. If I delete the first file before running the macro, it then hangs on the second file instead.
Without debugging, the first file will import, display on-screen, and there it stops. If I put a break-point on the very next instruction after the import, that break-point is never reached. THINGS I'VE TRIED:.......
View 2 Replies
View Related
Aug 8, 2012
I inherited a spreadsheet to manage that is linked to a SharePoint table.
It is trying to populate a date that a certain "Tier" is selected (1, 2, 3, or 4).
It works great if I manually type in the tiers, but does not run on existing data (about 400 records) or lines that are updated and new via the SharePoint list.
How can I have this run on all of the existing lines and anything added or changed in the future from the list?
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("AD2:AD10000")) Is Nothing Then
Application.EnableEvents = False
[Code] ....
View 9 Replies
View Related
Jun 20, 2008
I am a complete newbie with the camera tool. Is there any way to make the camera tool dynamic so that the cells the tool is referencing can change upon the click of a button or some other form of switch? I am creating my first dashboard and this is the last step for my report to be completely dynamic.
-----------------------------------------------------------------------
Never mind, I apologize i just figured this one out...
Create a user defined formulaIn the UDF Create an if statementThe if statement's condition is the logistics in the shift mechanismThe value if true is the first group of cells you want in the snap shotThe value if false is the second group of cells you want in the snap shot, The range you want to toggle betweenRename the cell reference of any snap shot you have taken with the UDF name. In my example my UDF is "Dude" so when you select the camera tool it shows =Dude as the cell referenceReference a scroll bar to the conditional cell you selected in the if statement or choose statement. This is the shift mechanism
If you want to toggle between more than 2 sets of data use =Choose instead of =if
View 3 Replies
View Related
Sep 12, 2006
Ive got a cell on a worksheet, that is controlled by a Control combo box. Is there anyway to change the value of the cell, and therefore the combo box, by using a macro. I need the macro to activate when the workbook is exited.
View 3 Replies
View Related
Jan 7, 2009
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.
View 9 Replies
View Related
Jul 23, 2014
I am trying to utilise a vba script for DNS resolution which i got from Followup: DNS Lookup and Ping in Excel - CodeProject
I have a list of IP addresses that I want to use this on, however if I test this by using the test instructions, excel does nothing, its just text in a cell, so I would like to know how would this procedure get called from the spreadsheet?
Code:
GetHostname("4.2.2.1") in any Excel cell.
or
Use: GetIpAddress("www.google.com") in any Excel cell.
View 3 Replies
View Related
Jul 31, 2014
I am trying to run a macro when any cell in a range changes. I have got this to run, but only on one cell, not any of the cells in a range.
Working code:
[Code]....
Non working Code:
[Code] .........
I am at a loss as to why the range code won't work, or why the first code won't work without makig the cell reference absolute.
View 3 Replies
View Related
Mar 25, 2014
I am new to the whole MACRO VBA thing and I am desperately trying to learn. So this is the Situation. I recorded a macro of an If statement on a row. very simple. Some of the rows below ( Like 700) need the same formula that I recorded. I would like to highlight the cells that need the formula and run the macro on them. however the macro only runs one at a time. Is there a way that this can be avoided?
View 11 Replies
View Related
Mar 23, 2012
Is it possible to have a macro run as soon as there is text typed into cell A2? Basically, I have a form that needs to be filled out, but somehow, users forget to put their name in the box. So I don't want any information to be able to be typed in until a name is entered.
View 9 Replies
View Related
Apr 17, 2012
I have following macro run when the given cell changes:
HTML Code:
Private Sub Case_Checkout(ByVal Target As Range)
Dim KeyCells As Range
Dim WS As Worksheet
Dim LkUpVal As Range
Dim LkUpRng As Range
[Code]...
It was working...and then just stopped working. I scan a barcode into cell A1 and hit enter. Can't figure out why is stopped unhiding the tab. I haven't changed anything...
View 1 Replies
View Related
Apr 29, 2008
I have a macro that is run by clicking on a macro button. The macro copies the data from Sheet1 and pastes it in another sheet, Sheet2. I added some conditional formatting that colors certain cells red if others are blank on Sheet1. I would like to add some code to my macro that will not allow it to copy and paste from sheet1 to sheet2 if there are any red cells in the range.
View 9 Replies
View Related
Feb 16, 2010
I need to check cell G5 (which is G4-G3) before running a macro. if G5 is zero i need to pop up a message to inform the user to enter data in cells G3 & G4. And if G5 is greater than Zero the macro shud run.
View 10 Replies
View Related
Feb 2, 2009
I have been trying to find a macro that would allow me to keep a running total in a cell by adding a number to that cell and the same cell would display the sum of the numbers entered. {i.e.} If cell a1 has 0 in it and i click on that cell and put 3 in it would display 3 if i added 2 to it it would display 5 and so on. I need it to do this for about 182 cells on one page.I found this macro that runs automatically every time and works great......But only for one cell so far..."D3"...Ugh!!! I need this macro to work for the following range of cells "colums d thru j from row 3 down to 28 in each colum. Each cell is an individual. There are 182 cells total that each need this macro to work ...all on one page
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Static dAccumulator As Double
With Target
If .Address(False, False) = "D3" Then
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
dAccumulator = dAccumulator + .Value
Else
dAccumulator = 0
End If
Application.EnableEvents = False
.Value = dAccumulator
Application.EnableEvents = True
End If
End With
End Sub
As you see the macro includes cell D3 only, I have made several attempts to include more cells but have failed.... I tried like this
If .Address(False, False) = "D3,E3,F3,G3,H3,I3,J3,D4,E4...and so on" Then
I have tried ....If .Address(False, False) = "D3:J3" Then
and I have tried ....If .Address(False, False) = "23Rx7C" Then
I can make it work for cell d3 but not the others. Will I have to repeat this code for every cell?
View 9 Replies
View Related
Oct 24, 2007
I've a workbook with 3 tabs with names "tab1, tab2, difference" and I've a macro that calculates tab1-tab2 and puts the results in tab "difference" This macro runs every time I open the workbook or every time dataset is refreshed on any tab.
everything works great but i've following issue.
let's say my cursor is in tab2 & i refresh/update dataset on tab2 which will trigger macro to update values in tab "difference".. this is good. but, soon after macro runs & updates tab "difference", cursor is no more in tab2 but in tab "difference" this is OK if I've 1 or 2 or few tabs but i've 25 tabs & the user has to go back to the initial tab everytime dataset is updated on any tab. Is there a way for the macro to recognize where the cursor initiallly was & run the macro & place the cursor back to the initial tab?
View 2 Replies
View Related
Dec 10, 2009
I have written some very basic code to format a report in excel. When I run the code it take a very long time to execute and I receive the following error message at the foot of the page:
Cell (press esc to cancel)
Annoyingly I have had this error before and found the solution on the web but can't remember where. If memory serves my right I deleted some temp files from a specific location on my hard drive?
View 3 Replies
View Related
Feb 21, 2009
I want to be able to select a cell, for example A10, this would then run a macro that would copy and paste several cells of information along that row and paste it on another worksheet. However, if i then choose a15, a3, a79 in turn (using the control key), it would run the macro using those rows in turn.
i'm looking forward to hearing if and particuliar how this could be achieved.
View 13 Replies
View Related
Jan 25, 2007
I have and issue within my For/Next loop. I'm simply gathering name and address data from columns in the workbook and printing them out (one at a time) onto a preprinted form letter. I use offsetts to move around the data row and get the appropriate items(address1, address2, city, etc). The first time through, it prints out. I'm then raising the row offset by 1 to get the second name and address data.
I'm using a static starting point on the data sheet but the second time through, I get a Runtime error 1004 at the point where it is supposed to select that starting point. Oddly, entering Debug I have done something which allows it to continue. It gets the second batch of data and prints properly. I have not been able to duplicate how that happened.
View 6 Replies
View Related
Jul 4, 2014
I have a problem with a nested loop in my macro as Excel hangs every time i run my script. I suspect it is because my sheets have too many elements.
I am doing a software localization, and I need to translate records in column A (english) to column X (slovenian) in the same row. With each new version of the software, the number of rows changes.
I already manually did one translation (44000 rows), and now i need to do a new version translation, so I want to use my old translation (because many if not most of the elements are the same) and insert values into the new excel file.
In the new file in sheet1 I have a full column A with english words and an empty C column with slovenian words that I need to fill.
Into sheet2 I copied the old english in column A and slovenian translation in column B.
On sheet 3 i created a button that when clicked, goes though each row in sheet1.columnA, compares it to sheet2.colimnA, and if there is a match, copies the value from sheet2.columnB into sheet1.columnC. So, this should fill most of the translations I need to do, and the rest I will do manually
I am quite new to VBA so this is what I came up with:
[Code]....
The number of rows is around 44000 in each sheet. How I could optimize or even completely avoid the nested loop, because each time i click the button I need to wait for almost an hour and then Excel hangs.
View 8 Replies
View Related
Sep 19, 2009
I want to make a combo box in excel that, if i select it the sheet inside the combo box will appear.
Example:
Inside of combox are: Sheet1
Sheet2
Sheet3
If i click combo box and i choose sheet3 the sheet3 will appear.
How can i do this? theirs a macro code to use?
View 9 Replies
View Related
Jan 30, 2008
I have been working on an excel spreadsheet and have come acrss this problem once before too. When I try to open this file it hangs and does not respond. the file size is not too big (490KB). When I disable macros i can open the same file but when the macro are enabled, it hangs. have been trying to look for a solution but just dont know what to do.
View 3 Replies
View Related
May 24, 2013
I have an issue with the excel freeze in our organisation.
Our organisation works on Citrix and the office applications have been upgraded to 2010 very recently.We have both 2003 excel users and 2010 excel users.There is a user who has a problem working on excel 2010 the excel freezes.Excel 2010 works fine for some time and all at a sudden hangs,once its hung it freezes all the other applications.
The same file runs properly on the other 2010 systems.The Excel file which hangs are also not of huge in size.
I am unable to get to the rootcause of the issue why the excel freezes or hangs in the middle of the work getting to the root cause of the issue and an answer for the excel hanging.
View 3 Replies
View Related
Apr 29, 2012
Consider this procedure to execute a MS Word mailmerge from within Excel:
Code:
Option Explicit
Dim objword As Object
Dim odoc As Object
Dim odoc2 As Object
Dim wdsendtonewdocument As Object
Dim mypath As String
Sub merge()
[Code] ........
The application hangs on the line in red. The file name (worksheets("Frontpage").Range("B15")) exists. It hangs with periodic dialogues "Microsoft Office is waiting for another application to complete an OLE action."
I end up having to go into task manager to close the word application before I can regain Excel control again.
View 9 Replies
View Related
Nov 3, 2012
Background: The user makes a selection from a drop down box on the main sheet (sheet5, G12). The drop-down box is linked to *Sheet31.Pax_Nav*. If the drop-down box's linked cell value is less than 5, then do nothing (manual input required). If it is greater than 5, then the vlookup matches that number to a person in the database and returns their weight. The code will pull the required person's weight but then Excel will hang and freeze.
Private Sub Worksheet_Calculate()
On Error Resume Next
If Sheet31.Range("Pax_Nav") > 5 Then
Sheet5.Range("G12").Value = Application.WorksheetFunction.VLookup(Sheet31.Range("Pax_Nav").Value, Sheet31.Range("H17:L48"), 5, False)
Else
End If
End Sub
View 9 Replies
View Related
Mar 26, 2006
I am trying to set up a combo box in my workbook so that when the item is selected from the list a particular macro will run according to the selection. I tried with the following codes but wonder why it is not executing when running the macro.
Option Explicit
Sub COUNTRYCURR2()
Dim shtInv As Worksheet
Dim shtT_Country As Worksheet
Dim Row As Long
Dim Row2 As Long
Dim lngEndRowInv As Long
Dim lngT_Country As Long
Set shtInv = Sheets("Inventory")
Set shtT_Country = Sheets("T_Country")
lngEndRowInv = shtInv. Range("A" & Rows.Count).End(xlUp).Row
lngT_Country = shtT_Country.Range("A" & Rows.Count).End(xlUp).Row
shtInv.Cells(1, 19).Value = "Country Code"
shtInv.Cells(1, 20).Value = "Currency Code".................
View 6 Replies
View Related
Sep 30, 2009
i need is a macro that once run selects a certain (or multiple) comboboxe's values. Eg I select macro 'test' and it selects value 1 on combo box 1, value 2 on combo box 2 etx..?
View 2 Replies
View Related
