Link to home
Start Free TrialLog in
Avatar of Terrygordon
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
Avatar of Edward Pamias
Edward Pamias
Flag of United States of America image

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.
Avatar of Terrygordon
Terrygordon

ASKER

Hi Edward

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

Open in new window

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

Open in new window

Hi,

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

Open in new window

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

Option Explicit

Sub x()
    Sheet1.Select
    Range("A1").Select
    ActiveCell.Value = "Hello World"
End Sub

Open in new window


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

Open in new window


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.
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

Open in new window

Check attach Sample, select any name and press Delete Names Button, it works.
Terry_DeleteNames.xlsm
Based on your code provided this will not move from sheet to sheet and should work as before

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

Open in new window


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
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.
There is nothing in the cose that I posted that would move between sheets.
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

  If cel.Value = pdname Then
            cel.EntireRow.Delete
        End If

Open in new window


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
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

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

Open in new window


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
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.