Alternative to select function in VBA?

just saw that Sktneer beat me to it (again!)


Its more likely that the problem occurs because you aren't pointing explicitly to the appropriate sheet.

to test this theory trying adding 'activesheet' in front of the Range method as in:

   

activesheet.Range("C28:D89,I28:J89,O28:P89,U28:V89").Select

Select allOpen in new window

However this assumes the currently active sheet is the one you are working with. to be more robust you would define the workbook and worksheet as variables and refer to them specifically eg::

Dim allData As Worksheet      'the output sheet
Dim theWorkbook As Workbook   'the workbook containing the sheet

Set theWorkbook = ActiveWorkbook       'or set to a specific workbook
Set allData = theWorkbook.Sheets("All")   ' ALL is the output sheet - modify according to tab name

theWorkbook.allData.Range("C28:D89,I28:J89,O28:P89,U28:V89").Select
....

Select allOpen in new window

Nackey,

Though i agree with sktneer that it's always advisable to make the code as specific as you can to make it more effective...

But what baffled me is that i'm not able to understand how the code is getting triggered in some other workbook because i'm assuming you are calling this by change event..and now if you are calling in the change event the code will only will execute when you are in that workbook..I mean you are actually have that workbook as active.. it doesn't matter if you have 10 different workbooks open and you are working on others..it shouldn't call the code of yours because that doesn't trigger the change event that you are referring here..

Can you advise me how you are calling or triggering this code...

Saurabh...

I've a pretty good feeling about the select code not been specific enough to the workbook as both of you suggested, so I'll try test that tomorrow.

And to clearify some things:
The users select input type from a dropdown list and that changes the dataformat of the cells below as shown in the picture.


Data is used in a graph in another worksheet.

The full code isn't a secret and in case you are interested the full code from this_workbook is:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Ark7.PageSetup
    .PrintArea = "$A$1:$O$37"
    .Zoom = False
    .FitToPagesTall = 1
    .FitToPagesWide = 1
End With

With Ark8.PageSetup
    .PrintArea = "$A$1:$O$37"
    .Zoom = False
    .FitToPagesTall = 1
    .FitToPagesWide = 1
End With
   
With Ark9.PageSetup
    .PrintArea = "$A$1:$O$37"
    .Zoom = False
    .FitToPagesTall = 1
    .FitToPagesWide = 1
End With

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Skjuler diverse ark
Ark2.Visible = xlSheetHidden
Ark4.Visible = xlSheetHidden
Ark5.Visible = xlSheetHidden

Dim txtFileName As String
 
    '1. Check of Save As was used
    If SaveAsUI = True And Application.UserName <> "my name" Then
        Cancel = True
 
    '2. Call up your own dialog box.  Cancel out if user Cancels in the dialog box
        txtFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
        If txtFileName = "False" Then
            'MsgBox "You didn't save", vbOKOnly
            Cancel = True
            Exit Sub
        End If
    '3. Save the file.
       Application.EnableEvents = False
        ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=52
        Application.EnableEvents = True
       
    End If


'If Application.UserName <> "my username" Then
'Exit Sub
'Else
'Dim txtFileName As String
 

'   If SaveAsUI = True Then
'        Cancel = True
 

'       txtFileName = Application.GetSaveAsFilename(, "Excel Macro-Enabled Workbook (*.xlsm), *.xlsm", , "Save As XLSM file")
'        If txtFileName = "False" Then
            'MsgBox "Action Cancelled", vbOKOnly
'            Cancel = True
'            Exit Sub
'        End If
   

'       Application.EnableEvents = False
'        ThisWorkbook.SaveAs Filename:=txtFileName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
'        Application.EnableEvents = True
       
'    End If
'End If
End Sub

Private Sub Workbook_Open()
'Sørger for at det altid er ark1 som der ses når regnearket åbnes
Ark1.Activate


End Sub

'Private Sub Workbook_WindowActivate(ByVal Wn As Window)

'Dim oCtrl As Office.CommandBarControl

'If Ark1.Cells(100, 1) = 0 Then

'Disable all Cut menus

'     For Each oCtrl In Application.CommandBars.FindControls(ID:=21)

'           oCtrl.Enabled = False

'    Next oCtrl

'Application.CellDragAndDrop = False
'Application.OnKey "^x", ""

'End If

'End Sub

'Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)


'Dim oCtrl As Office.CommandBarControl

'Enable all Cut menus

'     For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
'
'            oCtrl.Enabled = True

'     Next oCtrl

'Application.CellDragAndDrop = True
'Application.OnKey "^x"

'End Sub

Select allOpen in new window

 and code from specific wooksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
'Låser arket op
Ark1.Unprotect Password:=1234

'Styrer input format
If Target.Address = "$C$10" Then
    
    Range("C28:D89,I28:J89,O28:P89,U28:V89").Select
    
    Select Case Cells(10, 6)
    
        Case 0
            Selection.NumberFormat = "0"
        
        Case 1
            Selection.NumberFormat = "0.0"
        
        Case 2
            Selection.NumberFormat = "0.00"
        
        Case 5
            Selection.NumberFormat = "0%"
        
        Case 6
            Selection.NumberFormat = "0.0%"
            
        Case 7
            Selection.NumberFormat = "0.00%"
            
    End Select
 
    Range("C10").Activate
    
End If

'Styrer output format
If Target.Address = "$C$11" Then

    Range("C16:C17,C19,E28:E89,K28:K89,Q28:Q89,W28:W89").Select
    
    Select Case Cells(11, 6)
    
        Case 0
            Selection.NumberFormat = "0"
        
        Case 1
            Selection.NumberFormat = "0.0"
        
        Case 2
            Selection.NumberFormat = "0.00"
        
        Case 5
            Selection.NumberFormat = "0%"
            
        Case 6
            Selection.NumberFormat = "0.0%"
            
        Case 7
            Selection.NumberFormat = "0.00%"
    
    End Select

    Range("C11").Activate
    
End If
   
'Styrer om input er tekst eller dato
If Target.Address = "$C$13" Then

    Range("B28:B89,H28:H89,N28:N89,T28:T89").Select
    
    Select Case Cells(13, 6)
    
    Case 0
        Selection.NumberFormat = "General"
        
    Case 1
        With Selection
            .NumberFormat = "mmm/yyyy"
            .HorizontalAlignment = xlLeft
        End With
    
    End Select
       
    Range("C13").Activate
'    Ark4.Calculate
    
End If


If Cells(19, 8) = "" Then
    Cells(19, 8) = "Vælg"
'    Ark4.Calculate
End If

If Cells(10, 3) = "" Then
    Cells(10, 3) = "Procent (0 decimaler)"
'    Ark4.Calculate
End If

If Cells(11, 3) = "" Then
    Cells(11, 3) = "Procent (0 decimaler)"
'    Ark4.Calculate
End If

If Cells(13, 3) = "" Then
    Cells(13, 3) = "Tekst"
'    Ark4.Calculate
End If


'Låser arket
Ark1.Protect Password:=1234

End Sub

Select allOpen in new window

If you have any suggestions for tidying up the code it would be much appreciated as I have taken over much of the code from someone else and I'm not an expert by no means. BUT my concern rigth now is getting rid of the crashes, and then the other things can be done later.

I ended up with Sktneer's solution, simply because I kept getting an error trying to do it like regmigrant suggested. I don't know yet wheter this works for sure or that I'm just lucky but so far Excel has not frozen.