S
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
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