Solved: None of my buttons work anymore

[pingwin77]

I had some of these buttons working before but now for some reason they are not. I looked at my old versions and nothing has changed in the coding.

All of the buttons on the top of sheet "Data 30 - Dev" have issues when they runs. The codes error out just after the command line "Sheets("Formulas").Select" What would be causing this?

Is there something wrong with the sheet reference or the cells in the "formulas" tab?

Thanks to anyone who takes a look at this! My boss is riding me to get this done and it keeps kicking butt. I don't know what else to do.

[pingwin77]

After doing a bit more research, I realized these worked as a macro that was executed by clicking a button. When I converted them over to a code run by a command button they no longer work. Why would that make a difference?

[lucas]

Try this. On your button code for Private Sub CommandButtonLMC_Click() you have this as the first part of the code:

[VBA]Sheets("Formulas").Select
.Range("B2:K7").Select
Selection.Copy[/VBA]

I replaced it with this and it ran that part of the code ok:

[VBA]Sheets("Formulas").Range("B2:K7").Copy[/VBA]

[pingwin77]

fantastic! those buttons work great now. I am still having issues with the buttons for the decimal places and the inch/metric. when I step through the code using F8, it runs fine until it goes to reference the formulas tab. I get a: Run-time error "1004" How do you set these references differently from an excel macro format?

[lucas]

What sheet is CommandButtonUnits_Click supposed to operate on?

The code probably needs to be qualified using a with statement..

[pingwin77]

It starts on the "Data30 - Dev" sheet and makes the number format changes, then goes to "Formulas" sheet and selects a few different ranges and changes the format of those ranges. The last step is to go back to sheet "Data30 - Dev" and go to cell A1.

Here is the code that is being used. TI worked as a macro but for some reason does not work now that it is set as a VBA code. The first portions of each "IF" statement work but they both hang up on one of the red lines. I'm not sure what one.

If Range("J9").Value = "Inch" Then
    Range("J9").Value = "Metric"
    Range("G12:K12").Select
    Selection.NumberFormat = "0.0000"
    Range("G13:K13").Select
    Selection.NumberFormat = "0.0000"
    Sheets("Formulas").Select
    Range("G28:K33").Select
    Selection.NumberFormat = "0.0000"
    Range("G35:K40").Select
    Selection.NumberFormat = "0.0000"
    Range("G42:K45").Select
    Selection.NumberFormat = "0.0000"
    Range("G47:K49").Select
    Selection.NumberFormat = "0.0000"
Else
    Range("J9").Value = "Inch"
    Range("G12:K12").Select
    Selection.NumberFormat = ".0000"
    Range("G13:K13").Select
    Selection.NumberFormat = ".0000"
    Sheets("Formulas").Select
    Range("G28:K33").Select
    Selection.NumberFormat = ".0000"
    Range("G35:K40").Select
    Selection.NumberFormat = ".0000"
    Range("G42:K45").Select
    Selection.NumberFormat = ".0000"
    Range("G47:K49").Select
    Selection.NumberFormat = ".0000"
End If
    Range("A1").Select

[lucas]

I did one button for you....the inch/metric button:
[VBA]Private Sub CommandButtonUnits_Click()
' Metric and Inch Change - Deviation Data Sheet
' Macro recorded 10/24/2007 by Mike Hemm
Application.ScreenUpdating = False

If ActiveSheet.Range("J9").Value = "Inch" Then
ActiveSheet.Range("J9").Value = "Metric"
ActiveSheet.Range("G12:K12").NumberFormat = "0.0000"
ActiveSheet.Range("G13:K13").NumberFormat = "0.0000"
Sheets("Formulas").Range("G2:K7").NumberFormat = "0.0000"
Sheets("Formulas").Range("G9:K14").NumberFormat = "0.0000"
Sheets("Formulas").Range("G16:K19").NumberFormat = "0.0000"
Sheets("Formulas").Range("G21:K23").NumberFormat = "0.0000"
Else
ActiveSheet.Range("J9").Value = "Inch"
ActiveSheet.Range("G12:K12").NumberFormat = ".0000"
ActiveSheet.Range("G13:K13").NumberFormat = ".0000"
Sheets("Formulas").Range("G2:K7").NumberFormat = ".0000"
Sheets("Formulas").Range("G9:K14").NumberFormat = ".0000"
Sheets("Formulas").Range("G16:K19").NumberFormat = ".0000"
Sheets("Formulas").Range("G21:K23").NumberFormat = ".0000"
End If
Range("A1").Select
End Sub[/VBA]

Notice how I got rid of all of the selects and qualified each line with the sheet it's supposed to be working on? This can be further simplified using with statements but I wanted you to see what was going on.

You don't have to select a sheet to do something to it......

[pingwin77]

wonderful. Thanks for all the help. I got thrown into these projects because I knew the most about macros in excel, which is not much. These forums have been a great help in learning. You guys are great!

[lucas]

Glad to help but you have a lot of work to do.

Pay special attention to my note that you don't have to select or activate a sheet to do something to it and to qualify each action with the object you wish for it to be performed on.