Formula or VBA code to do Data Entry in Hidden and protected Sheet from Data entry Sheet

[Rahulkr]

Hi Everyone, Happy New year to all Great ones.
Please help in achieving my task, I have tried to some macros and some vba code to do some work in this workbook, actually I have prepared this excel for stock and inventory management as per our requirement. In this workbook user can do following task:-
1. first select entry type, either purchase or stock out from dropdown list
2. do entries and press on purchase or stock out shape accordingly. If press purchase then automatically data will go on purchase sheet and save it and return back to home sheet for new entry and so on for same as stock out also.
3. macro should automatically save data after each entry.
4. user can navigate in every sheet, but all sheets should be password protected to avoid any manipulation.
5. and if any cell in Home sheet is blank as per purchase or stock out criteria then macro should not run and throw message for either purchase or stock out.

But still I am getting errors and not able to achieve the task.

Each sheets password is 123 and VBA project password is 1236.

Any help is highly appreciated. Thanks a lot!

Some codes and macros which I have tried

In home sheet, for hidding the sheets and for combobox

Private Sub ComboBox2_GotFocus()
ComboBox2.ListFillRange = "DropDownList"
ComboBox2.DropDown
End Sub

Private Sub Worksheet_Activate()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
If ActiveSheet.Name <> ws.Name Then
ws.Visible = False
End If
Next ws

End Sub

Usings shapes, tried to navigate between all sheets.

[CODE=xls]
Sub JumpToSheet()
    Dim shp As Shape

    Set shp = ActiveSheet.Shapes(Application.Caller)
    With Worksheets(shp.Name)
        .Visible = True
        .Select
    End With
End Sub

[/CODE]

and macros to copy and paste the data in hidden and protected sheets

Sub pur()
'
' pur Macro
'

'
    Range("E10:E14").Select
    Sheets("HOME").Select
    Sheets("PURCHASE").Visible = True
    ActiveSheet.Unprotect
    Sheets("HOME").Select
    Selection.Copy
    Sheets("HOME").Select
    Sheets("PURCHASE").Visible = True
    Range("A5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Range("A5").Select
    Application.CutCopyMode = False
    Selection.ListObject.ListRows.Add (1)
    Range("A5").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("HOME").Select
    Selection.ClearContents
    Range("E10").Select
    ActiveWorkbook.Save
End Sub
Sub stout()
'
' stout Macro
'

'
    Range("E10:E13").Select
    Sheets("HOME").Select
    Sheets("STOCK OUT").Visible = True
    ActiveSheet.Unprotect
    Range("A5").Select
    Sheets("HOME").Select
    Selection.Copy
    Sheets("HOME").Select
    Sheets("STOCK OUT").Visible = True
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Range("A5").Select
    Application.CutCopyMode = False
    Selection.ListObject.ListRows.Add (1)
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("HOME").Select
    Selection.ClearContents
    Range("E10").Select
    ActiveWorkbook.Save
End Sub

This is the file link where you can see the full file
STATIONARY INVENTORY AND STOCK.xlsm

 

[Rahulkr]

Try the following macro for the "PURCHASE" button. It is not necessary to make the sheet visible.
The macro does the following:
- Verify the data.
- unprotect the sheet
- add a row to the end of table2
- copy the data
- Protect the sheet again.

Try to replicate the code for the "STOCK OUT" button.

Sub pur()
  Dim r As Range, c As Range
  Dim iRow As Long
 
  Set r = Range("E10:E14")
  For Each c In r
    If c.Value = "" Then
      MsgBox "Data is missing: " & c.Offset(, -1)
      Exit Sub
    End If
  Next
  With Sheets("PURCHASE")
    .Unprotect "123"
    With .ListObjects("Table2")
      .ListRows.Add AlwaysInsert:=True
      iRow = .DataBodyRange.Rows.Count
      .DataBodyRange(iRow, 1).Resize(1, 5).Value = Application.Transpose(r.Value)
    End With
    .Protect "123"
  End With
End Sub

Hi Dante, sorry again to disturb you. its highly appreciated your help. Now again I am having some issues while doing data entry and I have done the changes in your code, but still it is not giving the actual output. Instead of pasting the data, it is pasting data as N/A at the last column, below is the code which you have given and in that code I have just changed the cell ranges.

 Sub St_OUT()
      Dim r As Range, c As Range
      Dim iRow As Long

     [B] Set r = Range("E10:E13,E15")[/B]
      For Each c In r
        If c.Value = "" Then
          MsgBox "Please Do Full Entry: " & c.Offset(, -1)
          Exit Sub
        End If
      Next
      With Sheets("STOCK OUT")
        .Unprotect "123654"
        With .ListObjects("Table3")
          .ListRows.Add AlwaysInsert:=True
          iRow = .DataBodyRange.Rows.Count
          .DataBodyRange(iRow, 1).Resize(1, 5).Value = Application.Transpose(r.Value)
        End With
        .Protect "123654"
        Sheets("HOME").Select
        [B]Range("E10:E13,E15").ClearContents[/B]
        ActiveWorkbook.Save
    MsgBox "Data Saved Please Enter New Data !"
      End With

    End Sub

When I am running the above code it is not pasting the data of the E15 cell value in the STOCK OUT sheet.

Below is the output what I am getting.

DATE	ENTRY NO	ITEM NAME	QUANTITY	Remarks
01 January 2022	BLRSO/1	Tissue Roll	5	#N/A

Can you please help me on this. You are so great in vba coding. As of now maximum of the task has been done, but only in remarks field the data is pasting as #N/A instead of the data what I need to copy and past in it.