Attached a Macro to Button

[Zsand]

Hi,

I attempted to attach the following Macro to a button. But when I tried to run it the following errors:

Complie Error Invalid inside procedure.

I need your help.

Thanks

Sub commandButton()

Option Explicit
'Dim MyArray(6, 4)
Dim MyArray(200, 4)
Public MyData As Range, c As Range

Private Sub cmbAdd_Click()
'next empty cell in column C
Set c = Range("c65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False 'speed up, hide task
'write userform entries to database
c.Value = Me.Textbox1.Value
c.Offset(0, 1).Value = Me.TextBox2.Value
c.Offset(0, 2).Value = Me.TextBox3.Value
c.Offset(0, 3).Value = Me.TextBox4.Value
c.Offset(0, 4).Value = Me.TextBox5.Value
c.Offset(0, 5).Value = Me.TextBox6.Value
c.Offset(0, 6).Value = Me.ComboBox1.Value
c.Offset(0, 7).Value = Me.TextBox7.Value
c.Offset(0, 8).Value = Me.ComboBox2.Value
c.Offset(0, 9).Value = Me.ComboBox3.Value
c.Offset(0, 10).Value = Me.TextBox8.Value
c.Offset(0, 11).Value = Me.TextBox9.Value
c.Offset(0, 12).Value = Me.TextBox10.Value

'clear the form
With Me
.Textbox1.Value = vbNullString
.TextBox2.Value = vbNullString
.TextBox3.Value = vbNullString
.TextBox4.Value = vbNullString
.TextBox5.Value = vbNullString
.TextBox6.Value = vbNullString
.ComboBox1.Value = vbNullString
.TextBox7.Value = vbNullString
.ComboBox2.Value = vbNullString
.ComboBox3.Value = vbNullString
.TextBox8.Value = vbNullString
.TextBox9.Value = vbNullString
.TextBox10.Value = vbNullString


End With
Application.ScreenUpdating = True
End Sub

 

[Norie]

Zsand

Just put Option Explicit at the top of the module, not inside the sub.

Option Explicit

Sub commandButton()

'Dim MyArray(6, 4)
Dim MyArray(200, 4)
Public MyData As Range, c As Range

Private Sub cmbAdd_Click()
'next empty cell in column C
Set c = Range("c65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False 'speed up, hide task
'write userform entries to database
c.Value = Me.Textbox1.Value
c.Offset(0, 1).Value = Me.TextBox2.Value
c.Offset(0, 2).Value = Me.TextBox3.Value
c.Offset(0, 3).Value = Me.TextBox4.Value
c.Offset(0, 4).Value = Me.TextBox5.Value
c.Offset(0, 5).Value = Me.TextBox6.Value
c.Offset(0, 6).Value = Me.ComboBox1.Value
c.Offset(0, 7).Value = Me.TextBox7.Value
c.Offset(0, 8).Value = Me.ComboBox2.Value
c.Offset(0, 9).Value = Me.ComboBox3.Value
c.Offset(0, 10).Value = Me.TextBox8.Value
c.Offset(0, 11).Value = Me.TextBox9.Value
c.Offset(0, 12).Value = Me.TextBox10.Value

'clear the form
With Me
.Textbox1.Value = vbNullString
.TextBox2.Value = vbNullString
.TextBox3.Value = vbNullString
.TextBox4.Value = vbNullString
.TextBox5.Value = vbNullString
.TextBox6.Value = vbNullString
.ComboBox1.Value = vbNullString
.TextBox7.Value = vbNullString
.ComboBox2.Value = vbNullString
.ComboBox3.Value = vbNullString
.TextBox8.Value = vbNullString
.TextBox9.Value = vbNullString
.TextBox10.Value = vbNullString


End With
Application.ScreenUpdating = True
End Sub