Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Entry for 50 Rows only
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Entry for 50 Rows only
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" skrev i meddelelsen ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto copy data rows between sheets depending on date entry | New Users to Excel | |||
Data entry on filtered rows. | Excel Worksheet Functions | |||
Save 60% on Data Entry, Data Conversion, Data Processing Services byOffshore-Data-Entry | Excel Programming | |||
Save 60% on Data Entry, Data Conversion, Data Processing Services byOffshore-Data-Entry | Excel Programming | |||
Data Entry Online, Data Format, Data Conversion and Data EntryServices through Data Entry Outsourcing | Excel Discussion (Misc queries) |