+ Reply to Thread
Results 1 to 2 of 2

Enter ID>>auto search for blank cell>>prevent closing of excel>>re-enter ID to close

  1. #1
    Registered User
    Join Date
    03-25-2016
    Location
    singapore
    MS-Off Ver
    2010/2013
    Posts
    15

    Post Enter ID>>auto search for blank cell>>prevent closing of excel>>re-enter ID to close

    The ideas is to prompt out a input box that ask the user to key in ID, number only value. then it will auto search for a first blank cell founded in column H to allow the barcode enter from there. while the user is key in the barcode away from the PC using Bluetooth barcode scanner, the excel must not close by others user. to close it, user must enter the same ID The code only work until the enter value is true then it wont select the cell


    Sub Enter_1()
    Dim data_1 As String
    Dim sCell As Variant
    Dim rslt As Boolean


    Do
    data_1 = InputBox(Prompt:="Enter Employee No.", Title:="Employee", Default:="Enter Employee No. here")
    If data_1 = "" Then
    QuestionToMessageBox = "Exit?"
    YesOrNoAnswerToMessageBox = MsgBox(QuestionToMessageBox, vbYesNo, "Cancel")
    End If
    If YesOrNoAnswerToMessageBox = vbNo Then
    data_1 = InputBox(Prompt:="Enter Employee No.", Title:="Employee", Default:="Enter Employee No. here")
    Else
    Exit Sub
    End If
    rslt = False
    If Not IsNumeric(data_1) Then
    MsgBox "You can only enter a number in this field"

    rslt = True
    Run (rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row)
    End If
    Loop While rslt


    End Sub
    Public Sub MessageBoxYesOrNoMsgBox()

    Dim YesOrNoAnswerToMessageBox As String
    Dim QuestionToMessageBox As String


    End Sub
    Public Sub SelectFirstBlankCell()
    Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer
    Dim currentRowValue As String
    sourceCol = 8 'column H has a value of 8
    rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row

    'for every row, find the first blank cell and select it
    For currentRow = 1 To rowCount
    currentRowValue = Cells(currentRow, sourceCol).Value
    If IsEmpty(currentRowValue) Or currentRowValue = "" Then
    Cells(currentRow, sourceCol).Select
    End If
    Next
    End Sub
    Sub Enter_2(ByVal data_1 As Long, ByRef data_1 As Long)
    Dim data_2 As String
    Dim rslt As Boolean

    Do
    data_2 = InputBox(Prompt:="Enter Employee No.", _
    Title:="Employee", Default:="Enter Enter Employee No. here")
    If data_2 = "" Then MsgBox ("Cancel")
    Exit Sub

    rslt = False
    If Not IsNumeric(data_2) Then MsgBox "You can only enter a number in this field"
    If rslt = True Then
    End If
    Loop While rslt

    If data_2 <> data_1 Then MsgBox "Enter correct E.No"
    End If

    End Sub

    Private Sub Force(ByVal data_1 As Long, ByRef data_2 As Long)
    Dim cancel As Boolean
    Dim allow_close As Boolean

    If data_1 = data_2 Then
    allow_close = True
    Else
    allow_close = False
    End If
    End Sub

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166
    Hello jiaweilee,

    Welcome to Excelforum. Be a part of large Excel community. Enjoy Learning.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Enter number in blank cell match number on sheet 2 & enter data from adjacent cell
    By Bikeman in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-10-2015, 08:36 PM
  2. [SOLVED] Prevent user from closing worksheet if cell is blank
    By maryren in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-15-2013, 12:11 PM
  3. Replies: 2
    Last Post: 07-19-2013, 10:13 AM
  4. search and enter specific information on the next blank line without deleting
    By jlo33 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-15-2013, 10:03 AM
  5. Auto enter data from cell above on enter
    By GSXR1000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-15-2006, 10:06 AM
  6. [SOLVED] Enter name on 1st worksheet and have it auto. enter on all others
    By pattyh in forum Excel General
    Replies: 4
    Last Post: 03-08-2006, 08:30 PM
  7. Open excel file, enter values, save & close
    By JCanyoneer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-04-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1