Solved: A Submit Button that Doesn?t

[PCMonitor]

I need to gather desired file permisions from people so: a work book with two sheets one launches a form via a onClick macro. The other is for the data entered into the form; inserted upon clicking the cmdSubmit button ( thats the idea anyway (the idea includes using the form over and over using submit and clear buttons so new entrys will fill under each other on the second sheet )

the cmdSubmit button gives me:

Complie Error:

Sintax Error

and Private Sub cmdSubmit_Click() lights up yellow


here is the "code:"



Private Sub cmdSubmit_Click()

ActiveWorkbook.Sheets("Permissions").Activate

Range("A2").Select

Do

If IsEmpty(ActiveCell) = True

Then

ActiveCell.Offset(0, 1).Select

End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = shareFolder.Value

ActiveCell.Offset(0, 1) = deScript.Value

ActiveCell.Offset(0, 2) = permModify.Value

ActiveCell.Offset(0, 3) = permRne.Value

ActiveCell.Offset(0, 4) = permList.Value

ActiveCell.Offset(0, 5) = permRead.Value

ActiveCell.Offset(0, 6) = permWrite.Value

End If

Range("A2").Select

End Sub


I know not what I have done does anyone know?

[XLGibbs]

Excel code doesn't need to specify the resulting True/False operator like C# or VB would.. the evaluation is automatic

so IsEmpty() is by default a True/False "question".

Change the

IsEmpty(ActiveCell) = True

to just

IsEmpty(ActiveCell)

[PCMonitor]

Thanks Gibbs, still has the same error

If IsEmpty(ActiveCell) still lights up red

[XLGibbs]

Your "Then" should be on the same line.

By the way, this code can be a little more efficient. Here is the same thing, but without Selects and Activates..

I assumed your intent in looping for IsEmpty was to find the first empty cell in Column A. My revision does that with no loop
Private Sub cmdSubmit_Click()
Dim wb As Workbook, ws As Worksheet, c As Range
Set wb = ActiveWorkbook
With wb
Set ws = .Sheets("Permissions").Activate
Set c = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'finds the next available cell in column A
c.Value = shareFolder.Value
c.Offset(0, 1) = deScript.Value
c.Offset(0, 2) = permModify.Value
c.Offset(0, 3) = permRne.Value
c.Offset(0, 4) = permList.Value
c.Offset(0, 5) = permRead.Value
c.Offset(0, 6) = permWrite.Value
End With


Range("A2").Activate

End Sub

[XLGibbs]

Which can be further reduced to :Private Sub cmdSubmit_Click()
Dim wb As Workbook, ws As Worksheet, c As Range
Set wb = ActiveWorkbook
With wb
Set ws = .Sheets("Permissions").Activate
Set c = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'finds the next available cell in column A
c.Resize(1, 7) = Array(shareFolder.Value, deScript.Value, permModify.Value, _
permRne.Value, permList.Value, permRead.Value, _
permWrite.Value)
End With


Range("A2").Activate

End Sub

[PCMonitor]

using that instead gets a object required error?

now i have this:

Private Sub cmdSubmit_Click()
ActiveWorkbook.Sheets("Permissions").Activate

Range("A2").Select

Do

If IsEmpty(ActiveCell) Then

ActiveCell.Offset(0, 1).Select

End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = shareFolder.Value

ActiveCell.Offset(0, 1) = deScript.Value
ActiveCell.Offset(0, 2) = permModify.Value
ActiveCell.Offset(0, 3) = permRne.Value

ActiveCell.Offset(0, 4) = permList.Value
ActiveCell.Offset(0, 5) = permRead.Value
ActiveCell.Offset(0, 6) = permWrite.Value


ActiveWorkbook.Sheets("Form").Activate

Range("A2").Select

End

End Sub


This submits the data to the Permissions sheet and takes the active cell back to the "form launch page" (which is what i was after) how ever launching the form again and submiting to the Permissions sheet obviousley doesnt work (excel locks) I Intended to fill a row with each submit but I think Im trying to tack it on the end or on top of the first entry my VBocabulary is very limited you see..

[XLGibbs]

'Try this one, the Set ws statement had an .Activate at the end causing 'the error. The reason you are having issues is the ActiveCell references and loop which aren't necessary. The error has been removed below, which places the data in the next available empty row on Sheets Permissions.


Private Sub cmdSubmit_Click()
Dim wb As Workbook, ws As Worksheet, c As Range
Set wb = ActiveWorkbook
With wb
Set ws = .Sheets("Permissions")
Set c = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
'finds the next available cell in column A
c.Resize(1, 7) = Array(shareFolder.Value, deScript.Value, permModify.Value, _
permRne.Value, permList.Value, permRead.Value, _
permWrite.Value)
End With


Range("A2").Activate



End Sub

[PCMonitor]

It works! thank you thankyou thank you

[XLGibbs]

It works! thank you thankyou thank you

:beerchug: Your welcome. Just mark the thread solved if you are all set