Data Entry for 50 Rows only

[Shazi]

Hi Everyone,

I want to make a Function that Restrict Entry in the 'Data' (sheet1)
Sheet once the data entry reached to row no. 54. by defalt I Run the
Macro "Rows_Hide"
it hides all the rows from 55 to the end of the sheet.

When user enter the data till row no. 55 then the message will appear,
the sheet is full, pls click on the Activation button.,

when user click on the activation button, the an other macro will
"Rows_Show" it will unhide the all rows in the Data sheet, after doing
this, user enable to enter the more data in Data Sheet.

how it will possible, pls guide me.


USERFORM CODE:
-----------------------------

Private Sub CommandButton1_Click()
Dim LastRow As Object

' Sheet1 is named "Data"

Set LastRow = Sheet1.Range("B65536").End(xlUp)

LastRow.Offset(1, 0).Value = Name1.Text
LastRow.Offset(1, 1).Value = Company.Text
LastRow.Offset(1, 2).Value = City.Text
LastRow.Offset(1, 3).Value = Mobile.Text
LastRow.Offset(1, 4).Value = Phone11.Text
LastRow.Offset(1, 5).Value = Phone22.Text
LastRow.Offset(1, 6).Value = Fax.Text
LastRow.Offset(1, 7).Value = EMail.Text

If MsgBox("One record is written, do you have more entries ?",
vbYesNo, "Title") = vbYes Then

Call UserForm_Initialize

Else
Unload Me
End If
End If

End Sub



NORMAL MODULE CODE:
-------------------------------------

Sub Rows_Hide()

Sheet1.Unprotect Password:="marketing"
Sheets("Data").Rows("55:65536").EntireRow.Hidden = True
Sheet1.Protect Password:="marketing"

End Sub


Sub Rows_Show()

Sheet1.Unprotect Password:="marketing"
Sheets("Data").Rows("7:65536").EntireRow.Hidden = False
Sheet1.Protect Password:="marketing"

End Sub

Regards.


Shahzad

 

[Per Jessen]

Hi

Try this:

Private Sub CommandButton1_Click()
Dim LastRow As Object

' Sheet1 is named "Data"

Set LastRow = Sheet1.Range("B65536").End(xlUp)

If LastRow.Row + 1 = 55 Then
msg = MsgBox("The sheet is full." & vbLf & _
"Please click on the Activation button.", vbInformation, "Warning")
Exit Sub
End If


LastRow.Offset(1, 0).Value = Name1.Text
LastRow.Offset(1, 1).Value = Company.Text
LastRow.Offset(1, 2).Value = City.Text
LastRow.Offset(1, 3).Value = Mobile.Text
LastRow.Offset(1, 4).Value = Phone11.Text
LastRow.Offset(1, 5).Value = Phone22.Text
LastRow.Offset(1, 6).Value = Fax.Text
LastRow.Offset(1, 7).Value = EMail.Text


If LastRow.Row + 1 = 54 Then Call Rows_Hide

If MsgBox("One record is written, do you have more entries ?", _
vbYesNo, "Title") = vbYes Then

Call UserForm_Initialize

Else
Unload Me
End If

End Sub

To make all rows visible, you have to refer to the same range which
previusly set .hidden =true.

BTW: It's not needed to use the EntireRow statement as you are already
refering to the row : sheet1.Rows("55:65536").Hidden= False

Sub Rows_Show()

Sheet1.Unprotect Password:="marketing"
Sheet1.Rows("55:65536").EntireRow.Hidden = False
Sheet1.Protect Password:="marketing"

End Sub

Regards,
Per

 

[Shazi]

Hi

Try this:

Private Sub CommandButton1_Click()
   Dim LastRow As Object

' Sheet1 is named "Data"

   Set LastRow = Sheet1.Range("B65536").End(xlUp)

If LastRow.Row + 1 = 55 Then
    msg = MsgBox("The sheet is full." & vbLf & _
         "Please click on the Activation button.", vbInformation, "Warning")
    Exit Sub
End If

    LastRow.Offset(1, 0).Value = Name1.Text
    LastRow.Offset(1, 1).Value = Company.Text
    LastRow.Offset(1, 2).Value = City.Text
    LastRow.Offset(1, 3).Value = Mobile.Text
    LastRow.Offset(1, 4).Value = Phone11.Text
    LastRow.Offset(1, 5).Value = Phone22.Text
    LastRow.Offset(1, 6).Value = Fax.Text
    LastRow.Offset(1, 7).Value = EMail.Text

If LastRow.Row + 1 = 54 Then Call Rows_Hide

If MsgBox("One record is written, do you have more entries ?", _
     vbYesNo, "Title") = vbYes Then

    Call UserForm_Initialize

    Else
      Unload Me
   End If

End Sub

To make all rows visible, you have to refer to the same range which
previusly set .hidden =true.

BTW:  It's not needed to use the EntireRow statement as you are already
refering to the row : sheet1.Rows("55:65536").Hidden= False

Sub Rows_Show()

    Sheet1.Unprotect Password:="marketing"
    Sheet1.Rows("55:65536").EntireRow.Hidden = False
    Sheet1.Protect Password:="marketing"

End Sub

Regards,
Per

"Shazi" <[email protected]> skrev i meddelelsen























- Show quoted text -

Hi,

Per Jessen ,

Thank you for your support, its working fine.

Regards.


shahzad