• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Error in my code

F

FranckNL

New Member
Hello,

When launching the attached code I get an error message: "Compile error: invalid next control variable reference".

Can you please help me?

Thanks,
Franck
 

Attachments

  • VBA_Code.pdf
    88.9 KB · Views: 4
FranckNL said:
Hello,

When launching the attached code I get an error message: "Compile error: invalid next control variable reference".

Can you please help me?

Thanks,
Franck
Click to expand...
Context:
A form has been set up in sheet A, and a table in a separate sheet B. In the form there are 31 fields to be filled in, in column C, but also a button "OK".

When pressing the button "OK", all values entered from the form should be automatically added as a new line in the table from the sheet B. It doesn't work if at least a single field is empty:

  • Display of an error message: "Please fill in the missing field(s)" and
  • Empty fields are highlighted.

The table from the sheet B has the form fields as column headers.
 
It is a bit of work to adjust everything to a good working file, but I am retired, I have time, In a few days you get an good working example.
 
See attached.
The table on the sheet 2a.HI database - automation needed to be made into a proper Excel table (listobject)
The line:
Code: 
wsForm.Range("C5:C10, CC14:C17, C22, C27:C28, C32:C34, C39:C44, C48:C50, C53:C56, C60:C61").ClearContents
won't work on the 1.Form - draft sheet because that sheet has been protected. I don't have the password to unprotect it.
I have deleted or commented-out unnecessary lines of code.

Edit post posting sorry @Belleke, didn't see you had posted until after I posted.
 

Attachments

  • Chandoo55539Historical Incident management template - final version - testb.xlsm
    151.2 KB · Views: 3
Hello @p45cal, many thanks for what you have done for me.

I have enabled macros on the file you sent back to me.
When trying to run the macro I get this error message: "Cannot run the macro "Chandoo55539Historical Incident management template - final version - testb'!ButtonOK_Click'. The macro may not be available in this workbook or all macros may be disabled"

Can you please help me?

Thanks
 
FranckNL said:
Hello @p45cal, many thanks for what you have done for me.

I have enabled macros on the file you sent back to me.
When trying to run the macro I get this error message: "Cannot run the macro "Chandoo55539Historical Incident management template - final version - testb'!ButtonOK_Click'. The macro may not be available in this workbook or all macros may be disabled"

Can you please help me?

Thanks
Click to expand...
@p45cal Meanwhile solved
 
@Belleke at the beginning yes, but meanwhile some changes need to be applied, and that rule (to make all fields mandatory) must be removed...

I will send a new version for this form in max 2 days (next Monday or Tuesday).

Many thanks to you @Belleke and to @p45cal!
 
Dear @p45cal and @Belleke,

The form has been updated and works as I want thanks to your contribution.

However I have another request to improve it:
I would like to add a new line that makes the first field of the form "Incident_id" automatically increment after the button "OK" has been pushed, the data have been successfully added to the next worksheet, and the content of the form has been cleared.

Are these lines correct - to be added at the end of the code?

Code: 
'Set new value to the cell in wsForm.Range("C5") after the clearing
wsForm.Range("C5") = wsForm.Range("C5") + 1
End Sub
 
Last edited by a moderator:
FranckNL said:
Dear @p45cal and @Belleke,

The form has been updated and works as I want thanks to your contribution.

However I have another request to improve it:
I would like to add a new line that makes the first field of the form "Incident_id" automatically increment after the button "OK" has been pushed, the data have been successfully added to the next worksheet, and the content of the form has been cleared.

Are these lines correct - to be added at the end of the code?

Code: 
'Set new value to the cell in wsForm.Range("C5") after the clearing
wsForm.Range("C5") = wsForm.Range("C5") + 1
End Sub
Click to expand...

I replaced what I added earlier today by the following:

>>> use code - tags <<<
Code: 
'This declares the data type of the variable "Incident_id" in wsForm.Range("C5")
Dim LstRw As Long

'This defines what the variable "Incident_id" in wsForm.Range("C5") is refer to
LstRow = wsDatabase.Cells(Rows.Count, "A").End(x1Up).Row

'This tells the textbox named ID to equal the value of the last used cell in column A from wsDatabase + 1
wsForm.Range("C5") = Cells(LstRw, "A").Value + 1

''' When executing the whole code, this message is displayed:
''' "Run-time error '1004': Application-defined or object-defined error'

''' and when clicking on the button "Debug" the following line is highlighted in yellow:
''' "LstRow = wsDatabase.Cells(Rows.Count, "A").End(x1Up).Row"

Can you please help me? Thanks a lot!
 
Last edited by a moderator:
That is what I made until now.
 

Attachments

  • Historical Incident management template - final version - testb.xlsm
    163.3 KB · Views: 4
Dear @Belleke, many thanks for what you have done.
Unfortunately the client wants a form with all fields immediately available - as displayed in the first version of the file I submitted a few days ago...
 
They want to have a ready-to-use form, and don't want to press any button to see everything...
However many thanks for your help; it's really appreciated!
 
Last edited:
Like this?
 

Attachments

  • Historical Incident management template - final version - testb.xlsm
    161 KB · Views: 3
Yes - but why the button "Open form" is displayed? Can it be hidden or removed?
(I am on the learning curve...)
 
You must log in or register to reply here.
Back
Top