Terrygordon
asked on
Stop macro from going to a different sheet in Excel
Hi
I have a sheet (EditDefaults) with a combobox that allows the user to remove names from a drop down list. The combobox is populated (in the properties window) from a range on another sheet (Defaults). Basically, the user selects a name from the combobox drop down list on the EditDefaults sheet, clicks a 'remove name' button and the macro:
Searches for the name in the range on the Defaults sheet
Deletes the cell on the Defaults sheet
Shifts the cells in the range up using Delete Shift:=xlUp.
The problem is that when I run the macro, it always takes me to the Defaults sheet where the xlUp command was executed and stays there. I have tried using screenupdating and also a command at the end of the macro to select a cell on the EditDefaults sheet, but it still goes to the Defaults sheet.
I want it to execute the xlUp command, but stay on the same sheet.
Any help greatly appreciated.
Regards
Terry
I have a sheet (EditDefaults) with a combobox that allows the user to remove names from a drop down list. The combobox is populated (in the properties window) from a range on another sheet (Defaults). Basically, the user selects a name from the combobox drop down list on the EditDefaults sheet, clicks a 'remove name' button and the macro:
Searches for the name in the range on the Defaults sheet
Deletes the cell on the Defaults sheet
Shifts the cells in the range up using Delete Shift:=xlUp.
The problem is that when I run the macro, it always takes me to the Defaults sheet where the xlUp command was executed and stays there. I have tried using screenupdating and also a command at the end of the macro to select a cell on the EditDefaults sheet, but it still goes to the Defaults sheet.
I want it to execute the xlUp command, but stay on the same sheet.
Any help greatly appreciated.
Regards
Terry
If you share the Macro here it will be easier for the experts to assist. Just click code and paste the macro in between the words code.
ASKER
Hi Edward
Code attached.
Regards
Terry
Code attached.
Regards
Terry
Sub Deletenames()
pdname = Sheets("Editdefaults").Range("F7").Value 'combobox linked cell
For Each cel In Sheets("Defaults").Range("C2:C20") 'range that is specified in combobox properties
If cel.Value = pdname Then
cel.Delete Shift:=xlUp
End If
Sheets("Editdefaults").Select
Range("A1").Select
Next cel
End Sub
ASKER
Sorry - this is the correct code.
Sub Deletenames()
pdname = Sheets("Editdefaults").Range("F7").Value 'combobox linked cell
For Each cel In Sheets("Defaults").Range("C2:C20") 'range that is specified in combobox properties
If cel.Value = pdname Then
cel.Delete Shift:=xlUp
End If
Next cel
Sheets("Editdefaults").Select
Range("A1").Select
End Sub
Hi,
Try below:
Try below:
Sub RemoveNames()
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim pdname As String
Dim cel As Range, RemRng As Range
Set Ws1 = Worksheets("EditDefaults")
Set Ws2 = Worksheets("Defaults")
Application.ScreenUpdating = False
pdname = Ws1.Range("F7").Value
Set RemRng = Ws2.Range("C2:C20")
For Each cel In RemRng
If cel.Value = pdname Then
cel.Delete shift:=xlUp
End If
Next cel
Ws1.Activate
Ws1.Range("A1").Select
Application.ScreenUpdating = True
End Sub
Do you mean a data validation list?
In VBA code it is rarely necessary to Select or Activate Sheets, Ranges or WorkBooks which is what your code is probably doing. This site gives you an idea of how to refer to sheets in your code.
Basically instead of
The above code will select the sheet then the range, so use something like this
One line of code to achieve the same thing from any sheet. Post your code and we can look at it and make the changes iif you can't manage yourself.
In VBA code it is rarely necessary to Select or Activate Sheets, Ranges or WorkBooks which is what your code is probably doing. This site gives you an idea of how to refer to sheets in your code.
Basically instead of
Option Explicit
Sub x()
Sheet1.Select
Range("A1").Select
ActiveCell.Value = "Hello World"
End Sub
The above code will select the sheet then the range, so use something like this
Sub y()
Sheet1.Range("A1").Value = "Hello World"
End Sub
One line of code to achieve the same thing from any sheet. Post your code and we can look at it and make the changes iif you can't manage yourself.
ASKER
Shums - The macro is removing the name and shifting the cells up, but it still sticks on the 'Defaults' sheet. Also, for some reason, the 'EditDefaults' sheet tab remains highlighted, even though it is now displaying the other sheet. You also have to click on the 'Defaults' sheet before it will let you select the 'EditDefaults' sheet tab.
As you mentioned sheet name EditDefaults in your initial post, but actual sheet name is Editdefaults. Try below:
Sub RemoveNames()
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim pdname As String
Dim cel As Range, RemRng As Range
Set Ws1 = Worksheets("Editdefaults")
Set Ws2 = Worksheets("Defaults")
Application.ScreenUpdating = False
pdname = Ws1.Range("F7").Value
Set RemRng = Ws2.Range("C2:C20")
For Each cel In RemRng
If cel.Value = pdname Then
cel.Delete shift:=xlUp
End If
Next cel
Ws1.Activate
Ws1.Range("A1").Select
Application.ScreenUpdating = True
End Sub
Check attach Sample, select any name and press Delete Names Button, it works.
Terry_DeleteNames.xlsm
Terry_DeleteNames.xlsm
Based on your code provided this will not move from sheet to sheet and should work as before
It depends how you have set up the data validation, but you will probably need to re-define the range used.
Attach an example workbook
Sub Deletenames()
Set pdname = Sheets("Editdefaults").Range("F7").Value 'combobox linked cell
For Each cel In Sheets("Defaults").Range("C2:C20") 'range that is specified in combobox properties
If cel.Value = pdname Then
cel.Delete Shift:=xlUp
End If
Next cel
End Sub
It depends how you have set up the data validation, but you will probably need to re-define the range used.
Attach an example workbook
ASKER
Shums - your spreadsheet works, but it is based on an excel drop down cell, whereas mine is based on a combobox with the value linked to a cell (F7).
No point in uploading the whole workbook - it is massive and the defaults are full of confidential data.
All the other solutions still leave me on the wrong sheet.
It seems that the code is going to the 'Defaults' sheet when it executes the Shift:=xlUp command, which might explain why it freezes on that sheet, although I am at a loss as to why?
I inserted a msgbox to test the cel values and it goes to the 'Defaults' sheet when it reaches a match and stays there while it executes the rest of the code - it just isn't responding to the activate or select commands.
No point in uploading the whole workbook - it is massive and the defaults are full of confidential data.
All the other solutions still leave me on the wrong sheet.
It seems that the code is going to the 'Defaults' sheet when it executes the Shift:=xlUp command, which might explain why it freezes on that sheet, although I am at a loss as to why?
I inserted a msgbox to test the cel values and it goes to the 'Defaults' sheet when it reaches a match and stays there while it executes the rest of the code - it just isn't responding to the activate or select commands.
There is nothing in the cose that I posted that would move between sheets.
ASKER
Hi Roy. I get that, but it is, nevertheless, moving to the other sheet when it hits the XlUP statement.
Is there other data on the sheet, can you use
Or convert the range to a Table and delete the ListRow, this will not delete any other data on that row. You can create a one column Table.
If cel.Value = pdname Then
cel.EntireRow.Delete
End If
Or convert the range to a Table and delete the ListRow, this will not delete any other data on that row. You can create a one column Table.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi All
I am still getting the same issue of the macro going to the sheet, but I have recoded it to avoid using xlUP completely. The combobox clears and refills whenever the sheet is activated and I have used to following code to achieve what I want.
However, given the time and effort you guys have put into this and the fact that your examples seemed to work in your own workbooks, I am going to award the points jointly to Shums and Roy.
Thanks for your input and suggestions.
Regards
Terry
I am still getting the same issue of the macro going to the sheet, but I have recoded it to avoid using xlUP completely. The combobox clears and refills whenever the sheet is activated and I have used to following code to achieve what I want.
Sub Deletenames()
pdname = Sheets("Editdefaults").Range("F7").Value
For Each cel In Sheets("Defaults").Range("C2:c20")
If cel.Value = pdname Then cel.ClearContents
Next cel
Sheets("EditDefaults").EditPDList.Clear
For Each cel In Sheets("Defaults").Range("C2:C30")
name1 = cel.Value
If Len(name1) > 1 Then Sheets("EditDefaults").EditPDList.AddItem (name1)
Next cel
End Sub
However, given the time and effort you guys have put into this and the fact that your examples seemed to work in your own workbooks, I am going to award the points jointly to Shums and Roy.
Thanks for your input and suggestions.
Regards
Terry
ASKER
Thanks
Pleased to help. I'm sure we could have got to the bottom of this if you had added a simplified version of your worksheet.