Alternative to select function in VBA?

I use a  excel woorkbook with some VBA in it and are excerpering a problem. The workbook works great when it's the only workbook open, but when other workbooks also are open, my workbook tend to crash when switching between workbooks and entering data in the cells.

I don't really know what's causing the problem, but I suspect it to be something with the use of SELECT. Therefore I want to get rid of the select functions to test if that is causing the crashes.

The code is attached to dropdown list (using datavalidation), there the users can choose between different types of how to show data:
 
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

Open in new window


Any alternatives to the SELECT function?
NackeyQualitative ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Well since you have not shared the full code, just to give you an idea here are some points....

Qualify the cell and range reference with the workbook and sheet to work properly.

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

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

Open in new window


And your Select Case statement should be something like this....

Select Case ThisWorkbook.Sheets("Sheet1").Cells(10, 6)

Open in new window


With above changes the code will always be executed on the workbook where the code resides. Of course you may change the sheet reference as per your requirement.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
regmigrantCommented:
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

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

Open in new window

1
Saurabh Singh TeotiaCommented:
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...
0
NackeyQualitative ConsultantAuthor Commented:
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.

Selecting-input.jpg
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

Open 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

Open 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.
0
NackeyQualitative ConsultantAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.