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

Rahulkr

Board Regular
Joined
Dec 10, 2019
Messages
66
Office Version
  1. 2010
Platform
  1. Windows
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
VBA Code:
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.

VBA Code:
[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

VBA Code:
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

Board Regular
Joined
Dec 10, 2019
Messages
66
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
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.

VBA Code:
 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.
DATEENTRY NOITEM NAMEQUANTITYRemarks
01 January 2022BLRSO/1Tissue Roll5#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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,409
Messages
5,831,445
Members
430,069
Latest member
bubbleboom

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top