On Aug 3, 12:30*pm, "Per Jessen" <per.jes...@mail.dk> wrote:
> 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" <shahzad4u_...@yahoo.com> skrev i meddelelsennews:f8f16895-ea07-4144-adb2-(E-Mail Removed)...
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -
Hi,
Per Jessen ,
Thank you for your support, its working fine.
Regards.
shahzad
|
Similar Threads
