Change Code - Input Boxes

[imcfcl]

I have shown below a small part of code that I use for creating new worksheets. It ask's the user for all of the data that needs to be shown on the new sheet.

    Sheets("NewSheetInfo").Select
    Range("c3").Select
    ActiveCell.Value = InputBox("Door Number?")
        Range("d3").Select
    ActiveCell.Value = InputBox("Location on Site?")
        Range("e3").Select
    ActiveCell.Value = InputBox("Door Type?")
        Range("f3").Select
    ActiveCell.Value = InputBox("Manufacturer?")
        Range("g3").Select
    ActiveCell.Value = InputBox("Motor Type?")
        Range("h3").Select
    ActiveCell.Value = InputBox("Operation - Electric/Manual/Automatic ?")
        Range("i3").Select
    ActiveCell.Value = InputBox("Usage? High or Low")

I need to add something to the above code for the first input box (door number) by which Column C, in a sheet called 'Database' will be checked to see if the entered number already exists. If the number has already been used, then everything messes up further down the line.

If the number entered already exists, I need an 'OK' box telling the user that the number they tried is already in use, and then the same input box for the user to try a different number. It would also be good if there could be a 'cancel' button to end sub if user doesn't have a unique number to use....

Hope that makes sense? Thanks in advance

 

[imcfcl]

I know this is fiddly, has anybody had any thoughts?

 

[John_w]

Start by doing a manual Find in column C on the Database sheet with the macro recorder and integrate that into your code.

 

[daverunt]

Here's some code you can try. I'm not sure if I've stuck it at the right point in your code. However it does pick up numbers in the Database column C range that I enter into the inputBox. Hope it helps. It's something to work on and improve.

Sheets("NewSheetInfo").Select
    Range("c3").Select
 
Set ws = Sheets("Database")
Set Rng = ws.Range(ws.Range("C1"), ws.Range("C" & Rows.Count).End(xlUp))
 
Do
ValidEntry = True
 
    ActiveCell.Value = InputBox("Door Number?")
 
    For Each Dn In Rng
 
    If ActiveCell.Value = Dn Or ActiveCell.Value = "" Then
 
    ValidEntry = False
 
    End If
    Next Dn
 
    If ValidEntry = False Then
    MsgBox ("Number in use, choose another")
    End If
 
    Loop Until ValidEntry
 
MsgBox("number not found") <-- remove     
 
      Range("d3").Select
   ActiveCell.Value = InputBox("Location on Site?")

 

[daverunt]

Minor modifcation - exits sub if user enters nothing or cancels:

Do
ValidEntry = True
       
    ActiveCell.Value = InputBox("Door Number?")
    If ActiveCell.Value = "" Then
     Exit Sub
    End If
    
    For Each Dn In Rng
 
    If ActiveCell.Value = Dn Then
     
    ValidEntry = False
    
    End If
    Next Dn
      
    If ValidEntry = False Then
    MsgBox ("Number in use, choose another")
    End If
         
    Loop Until ValidEntry
    MsgBox ("Good Number")

 

[imcfcl]

Hi there,

apologies for missing this, I managed to knock something up before I realised you had responded.

I just added a cell doing a vlookup on the Door Number in Col C in the database, and and iferrror cell in C4 and C5, which meant if there number wasn't there, vlookup would error, and c5 would read 'continue'.

Does the below look ok? Or woul dyour code be better?

    Sheets("NewSheetInfo").Select
    
Dim test As String
 Do
    Range("c3").Select
    ActiveCell.Value = InputBox("Door Number?")
    
    If ActiveCell.Value = "" Then
    Sheets("NewSheetInfo").Visible = False
    Sheets("Home").Activate
    MsgBox = "You Cancelled"
    Exit Sub
    End If
    
    test = Range("C5").Value
    MsgBox "Door Reference number already Exists - Pick another number"
    Loop Until test = "continue"

 

[GTO]

Maybe I read too quickly, but have you tried developing a USerForm?

 

[imcfcl]

Morning GTO,

I'm still very new to VBA, a user form wasn't something I thought off, everything seems to work as it is now.

I'm just always interested in other peoples ways and means... as I say, I'm new to it all but do want to get better.

 

[daverunt]

If your code works for you and you understand what it is doing then use that.