hello all,
i have a user for that has several option buttons and a ok/cancle button. after the user selects one of the option buttons and hits ok the code is suppose to call the macro that pertains to that option button and the first step in all the differnt codes is to creat a new sheet. but when i try and run one code it does nothing
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub
Public Sub btnOK_Click()
Do
If optAGSTemplate = True Then
Call MCRClassAGSTemplate.MCRClassAGSTemplate
ElseIf optbtnAGATemplate = True Then
Call MCRAGATemplate.MCRAGATemplate
End If
Unload frmTemplates
Exit Do
Loop
End Sub
Private Sub btnCancel_Click()
Unload frmTemplates
End Sub
here is the code that is having the issues:
Sub MCRAGATemplate()
'
' MCRAGATemplate Macro
Sheets("Instructions").Select
Sheets.Add
Sheets("sheet1").Select
Sheets("sheet1").Name = "AGA Template"
Range("A1").Select
ActiveCell.FormulaR1C1 = "Description"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Point Type ID"
'Input Description
Range("A2").Select
ActiveCell.FormulaR1C1 = "Unclassified"
Range("A3").Select
ActiveCell.FormulaR1C1 = "CP Test Point"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Casing Vent"
Range("A5").Select
ActiveCell.FormulaR1C1 = "CL Road"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Fenceline Crossing"
Range("A7").Select
ActiveCell.FormulaR1C1 = "AGM Placement"
Range("A8").Select
ActiveCell.FormulaR1C1 = "Pipeline Marker"
Range("A9").Select
ActiveCell.FormulaR1C1 = "Valve"
Range("A10").Select
ActiveCell.FormulaR1C1 = "Pipeline Feature"
Range("A11").Select
ActiveCell.FormulaR1C1 = "Navigation Stake Target"
Range("A12").Select
ActiveCell.FormulaR1C1 = "Rectifier"
Range("A13").Select
ActiveCell.FormulaR1C1 = "Control Point"
Range("A14").Select
ActiveCell.FormulaR1C1 = "Arial Marker"
Range("A15").Select
ActiveCell.FormulaR1C1 = "Foreign Crossing"
Range("A16").Select
ActiveCell.FormulaR1C1 = "Mile Post"
Range("A17").Select
ActiveCell.FormulaR1C1 = "Kilometer Post"
Range("A18").Select
ActiveCell.FormulaR1C1 = "Projected Profile"
Range("A19").Select
ActiveCell.FormulaR1C1 = "CL RR Crossing"
Range("A20").Select
ActiveCell.FormulaR1C1 = "Edge of Water Crossing"
Range("A21").Select
ActiveCell.FormulaR1C1 = "CL Water Crossing"
Range("A22").Select
ActiveCell.FormulaR1C1 = "PI"
Range("A23").Select
ActiveCell.FormulaR1C1 = "Estimated REF Valve"
Range("A24").Select
ActiveCell.FormulaR1C1 = "HCA REF Point"
Range("A25").Select
Columns("A:B").Select
'Input Point Type ID
Range("B2").Select
ActiveCell.FormulaR1C1 = "0"
Range("B3").Select
ActiveCell.FormulaR1C1 = "1"
Range("B4").Select
ActiveCell.FormulaR1C1 = "2"
Range("B5").Select
ActiveCell.FormulaR1C1 = "3"
Range("B6").Select
ActiveCell.FormulaR1C1 = "4"
Range("B7").Select
ActiveCell.FormulaR1C1 = "5"
Range("B8").Select
ActiveCell.FormulaR1C1 = "6"
Range("B9").Select
ActiveCell.FormulaR1C1 = "7"
Range("B10").Select
ActiveCell.FormulaR1C1 = "8"
Range("B11").Select
ActiveCell.FormulaR1C1 = "9"
Range("B12").Select
ActiveCell.FormulaR1C1 = "10"
Range("B13").Select
ActiveCell.FormulaR1C1 = "11"
Range("B14").Select
ActiveCell.FormulaR1C1 = "12"
Range("B15").Select
ActiveCell.FormulaR1C1 = "13"
Range("B16").Select
ActiveCell.FormulaR1C1 = "14"
Range("B17").Select
ActiveCell.FormulaR1C1 = "15"
Range("B18").Select
ActiveCell.FormulaR1C1 = "16"
Range("B19").Select
ActiveCell.FormulaR1C1 = "17"
Range("B20").Select
ActiveCell.FormulaR1C1 = "18"
Range("B21").Select
ActiveCell.FormulaR1C1 = "19"
Range("B22").Select
ActiveCell.FormulaR1C1 = "100000"
Range("B23").Select
ActiveCell.FormulaR1C1 = "100002"
Range("B24").Select
ActiveCell.FormulaR1C1 = "100003"
Range("B25").Select
'Center and wrap text
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'Auto Fit column
Columns("A:B").EntireColumn.AutoFit
Columns("A:B").EntireColumn.AutoFit
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:D1").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'Selection.Merge
Range("C1").Select
ActiveCell.FormulaR1C1 = "Place Source Data Here"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Marker Location# or Point ID From PICS 10 Results "
Range("D2").Select
ActiveCell.FormulaR1C1 = "AGA Type"
Range("E2").Select
ActiveCell.FormulaR1C1 = "@"
Range("F2").Select
ActiveCell.FormulaR1C1 = "AGA Description"
Range("G2").Select
ActiveCell.FormulaR1C1 = "Latitude"
Range("H2").Select
ActiveCell.FormulaR1C1 = "Longitude"
Range("I2").Select
ActiveCell.FormulaR1C1 = "Elevation"
Range("J2").Select
ActiveCell.FormulaR1C1 = "Comments "
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("A1:J65").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$3:$A$25"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Rows("2:2").RowHeight = 63
Columns("C:C").ColumnWidth = 14
Columns("C:C").ColumnWidth = 19.86
Columns("C:C").ColumnWidth = 23
Rows("2:2").RowHeight = 45
Columns("H:H").ColumnWidth = 9.86
Columns("I:I").EntireColumn.AutoFit
Columns("I:I").ColumnWidth = 13.71
Columns("J:J").ColumnWidth = 12.29
Columns("F:F").ColumnWidth = 15.86
Columns("A:B").Select
Selection.EntireColumn.Hidden = True
'Adds Button for Format template
Range("I1:J1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("I1:J1").Select
ActiveSheet.Buttons.Add(423, 7.5, 72, 72).Select
Selection.OnAction = _
"'Busby''s Utility Templates and Formatting.xlsb'!MCRAGAFormat.MCRAGAFormat"
ActiveSheet.Shapes("Button 3").IncrementTop -7.5
ActiveSheet.Shapes("Button 3").IncrementLeft -3
ActiveSheet.Shapes("Button 3").IncrementTop 9
ActiveSheet.Shapes("Button 3").ScaleWidth 2.701754386, msoFalse, _
msoScaleFromTopLeft
Rows("1:1").RowHeight = 32.25
Selection.Characters.Text = "Format AGA"
With Selection.Characters(Start:=1, Length:=10).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
End Sub
Please advise
i have a user for that has several option buttons and a ok/cancle button. after the user selects one of the option buttons and hits ok the code is suppose to call the macro that pertains to that option button and the first step in all the differnt codes is to creat a new sheet. but when i try and run one code it does nothing
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub
Public Sub btnOK_Click()
Do
If optAGSTemplate = True Then
Call MCRClassAGSTemplate.MCRClassAGSTemplate
ElseIf optbtnAGATemplate = True Then
Call MCRAGATemplate.MCRAGATemplate
End If
Unload frmTemplates
Exit Do
Loop
End Sub
Private Sub btnCancel_Click()
Unload frmTemplates
End Sub
here is the code that is having the issues:
Sub MCRAGATemplate()
'
' MCRAGATemplate Macro
Sheets("Instructions").Select
Sheets.Add
Sheets("sheet1").Select
Sheets("sheet1").Name = "AGA Template"
Range("A1").Select
ActiveCell.FormulaR1C1 = "Description"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Point Type ID"
'Input Description
Range("A2").Select
ActiveCell.FormulaR1C1 = "Unclassified"
Range("A3").Select
ActiveCell.FormulaR1C1 = "CP Test Point"
Range("A4").Select
ActiveCell.FormulaR1C1 = "Casing Vent"
Range("A5").Select
ActiveCell.FormulaR1C1 = "CL Road"
Range("A6").Select
ActiveCell.FormulaR1C1 = "Fenceline Crossing"
Range("A7").Select
ActiveCell.FormulaR1C1 = "AGM Placement"
Range("A8").Select
ActiveCell.FormulaR1C1 = "Pipeline Marker"
Range("A9").Select
ActiveCell.FormulaR1C1 = "Valve"
Range("A10").Select
ActiveCell.FormulaR1C1 = "Pipeline Feature"
Range("A11").Select
ActiveCell.FormulaR1C1 = "Navigation Stake Target"
Range("A12").Select
ActiveCell.FormulaR1C1 = "Rectifier"
Range("A13").Select
ActiveCell.FormulaR1C1 = "Control Point"
Range("A14").Select
ActiveCell.FormulaR1C1 = "Arial Marker"
Range("A15").Select
ActiveCell.FormulaR1C1 = "Foreign Crossing"
Range("A16").Select
ActiveCell.FormulaR1C1 = "Mile Post"
Range("A17").Select
ActiveCell.FormulaR1C1 = "Kilometer Post"
Range("A18").Select
ActiveCell.FormulaR1C1 = "Projected Profile"
Range("A19").Select
ActiveCell.FormulaR1C1 = "CL RR Crossing"
Range("A20").Select
ActiveCell.FormulaR1C1 = "Edge of Water Crossing"
Range("A21").Select
ActiveCell.FormulaR1C1 = "CL Water Crossing"
Range("A22").Select
ActiveCell.FormulaR1C1 = "PI"
Range("A23").Select
ActiveCell.FormulaR1C1 = "Estimated REF Valve"
Range("A24").Select
ActiveCell.FormulaR1C1 = "HCA REF Point"
Range("A25").Select
Columns("A:B").Select
'Input Point Type ID
Range("B2").Select
ActiveCell.FormulaR1C1 = "0"
Range("B3").Select
ActiveCell.FormulaR1C1 = "1"
Range("B4").Select
ActiveCell.FormulaR1C1 = "2"
Range("B5").Select
ActiveCell.FormulaR1C1 = "3"
Range("B6").Select
ActiveCell.FormulaR1C1 = "4"
Range("B7").Select
ActiveCell.FormulaR1C1 = "5"
Range("B8").Select
ActiveCell.FormulaR1C1 = "6"
Range("B9").Select
ActiveCell.FormulaR1C1 = "7"
Range("B10").Select
ActiveCell.FormulaR1C1 = "8"
Range("B11").Select
ActiveCell.FormulaR1C1 = "9"
Range("B12").Select
ActiveCell.FormulaR1C1 = "10"
Range("B13").Select
ActiveCell.FormulaR1C1 = "11"
Range("B14").Select
ActiveCell.FormulaR1C1 = "12"
Range("B15").Select
ActiveCell.FormulaR1C1 = "13"
Range("B16").Select
ActiveCell.FormulaR1C1 = "14"
Range("B17").Select
ActiveCell.FormulaR1C1 = "15"
Range("B18").Select
ActiveCell.FormulaR1C1 = "16"
Range("B19").Select
ActiveCell.FormulaR1C1 = "17"
Range("B20").Select
ActiveCell.FormulaR1C1 = "18"
Range("B21").Select
ActiveCell.FormulaR1C1 = "19"
Range("B22").Select
ActiveCell.FormulaR1C1 = "100000"
Range("B23").Select
ActiveCell.FormulaR1C1 = "100002"
Range("B24").Select
ActiveCell.FormulaR1C1 = "100003"
Range("B25").Select
'Center and wrap text
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'Auto Fit column
Columns("A:B").EntireColumn.AutoFit
Columns("A:B").EntireColumn.AutoFit
Rows("1:1").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1:D1").Select
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'Selection.Merge
Range("C1").Select
ActiveCell.FormulaR1C1 = "Place Source Data Here"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Marker Location# or Point ID From PICS 10 Results "
Range("D2").Select
ActiveCell.FormulaR1C1 = "AGA Type"
Range("E2").Select
ActiveCell.FormulaR1C1 = "@"
Range("F2").Select
ActiveCell.FormulaR1C1 = "AGA Description"
Range("G2").Select
ActiveCell.FormulaR1C1 = "Latitude"
Range("H2").Select
ActiveCell.FormulaR1C1 = "Longitude"
Range("I2").Select
ActiveCell.FormulaR1C1 = "Elevation"
Range("J2").Select
ActiveCell.FormulaR1C1 = "Comments "
Cells.Select
With Selection
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("A1:J65").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("D3").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$A$3:$A$25"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Rows("2:2").RowHeight = 63
Columns("C:C").ColumnWidth = 14
Columns("C:C").ColumnWidth = 19.86
Columns("C:C").ColumnWidth = 23
Rows("2:2").RowHeight = 45
Columns("H:H").ColumnWidth = 9.86
Columns("I:I").EntireColumn.AutoFit
Columns("I:I").ColumnWidth = 13.71
Columns("J:J").ColumnWidth = 12.29
Columns("F:F").ColumnWidth = 15.86
Columns("A:B").Select
Selection.EntireColumn.Hidden = True
'Adds Button for Format template
Range("I1:J1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("I1:J1").Select
ActiveSheet.Buttons.Add(423, 7.5, 72, 72).Select
Selection.OnAction = _
"'Busby''s Utility Templates and Formatting.xlsb'!MCRAGAFormat.MCRAGAFormat"
ActiveSheet.Shapes("Button 3").IncrementTop -7.5
ActiveSheet.Shapes("Button 3").IncrementLeft -3
ActiveSheet.Shapes("Button 3").IncrementTop 9
ActiveSheet.Shapes("Button 3").ScaleWidth 2.701754386, msoFalse, _
msoScaleFromTopLeft
Rows("1:1").RowHeight = 32.25
Selection.Characters.Text = "Format AGA"
With Selection.Characters(Start:=1, Length:=10).Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
End With
End Sub
Please advise