VBA for Beginners - VBA Input Box

VBA for Beginners – VBA Input Box



Views:71180|Rating:4.73|View Time:14:39Minutes|Likes:275|Dislikes:16
VBA Input Box. The input box in VBA is a marvellous feature that will enable us to collect and add data to and from our worksheet. It is easy to construct in use and can be used in conjunction with the Select Case Function and the If function. This is an invaluable tool to aid the user in working effectively with your application. The input box can be used as either a Function or a Method and in this video I will show you the difference between the two. We will be developing a login example that allows the user to login their details in time as they enter the workbook. VBA input box is a wonderful feature that we should incorporate regularly into our code.

well greetings welcome along this is trip from online PC learning comm VBA for beginners is our tutorial we're going to have a look at the VBA input box well look at the function and the method this is a marvelous tool for helping the user add data and to make decisions let's have a look at what we'll be looking at in this tutorial first of all we'll have a look at what our input boxes I will show you how to construct a simple VBA input box we'll have a look at the syntax in other words what makes it tick and we'll look at that step-by-step then we're going to create a user login with the input box well have a look at the types order that you can use within the input box method and the difference between the method and the function and then finally I'm going to take you through how to make that user login a little bit more complex a little bit more versatile and I'll also show you how to use the input box to be able to set and print an area on your spreadsheet so there's some really interesting stuff so stay tuned let's get going first of all what our input boxes well the input boxes could be best described as a message box that lets you send data you can send data to your spreadsheet you can send data as a variable and show it in a message box you can do all sorts of things with input boxes here is a picture of an input box it allows you to put in a title and type your name and you can send us we're showing you here we're sending login information to a worksheet based on what the user is putting in an input box so it's a box like a message box that allows you to send data here's the code and I'm going to show you how to put this in to your VBA editor in a moment into a module now basically all this does is first of all we're dimming a variable that we've set you need a variable and expression and here it is I've called it add name call it whatever you like but it's nice to call it something that is familiar to the procedure you're doing I'm adding a name to a worksheet here so I've called the variable add 9 and then it's equal input box now input box as you see here is the keyword that's the function keyword when you put that in it then and put in our left parenthesis then the syntax will come up and walk you through how to add all of these options now in here I've put the message which says type your first name you see it here there it is there and there add nine is the title in up here and then type nine here is what we've put in the box to prompt the user what to do and if we want to text we can say type your name here use text or whatever and then once we do that we add the value we're saying range a 1 the active worksheet range a 1 die value equals add name which is the value in that input box right so I put in a procedure here called sub input test let's add in a message box so we're going to call this add name and then we'll equal sign and type in in and then open parenthesis now notice as soon as we do that we are given the prompt here that tells us what we need to do now mandatory is that we need to put in the prompt we need to put in between double quotes what we want to say that this message box is going to tell the user to do so we're put in here double quotes type your name here and there now if we were to run this let's click outside it you'll notice they'll go to uppercase if we were to run this we're going to get a simple input box let's run it with the f5 key there's the simple input box that comes up now if we can take that a step further as we showed you in that slide now here's the same procedure the only thing we've done is just said now range a1 dot value equals the variable add name so let's run that and see what happens so hit the f5 key and then we're going to type in the name and then go okay what happens while the name is added to cell a1 we're collecting information from the input box adding it to our data set what a marvelous thing we don't need to even use a user forum to do that now before we go any further let's go back to our slide and let's go through the syntax that is involved so here is the syntax now all of this information is going to be on the website article by the way but this is just to show you briefly what you can do with an input box so here's the variable that you set then the input box this is the function we're dealing with and then we have the prompt which is mandatory and that's why I've put it in red the second is the title the third is the default in other words what you want inside the box four and five are the position left and top so it just means where you want the message box to appear on the screen you can set that position now you're going to really use left and top and help you will really use them but what you will use is type over here and you get tight by adding a word application in front of your message box but leave it in here for now and I'll show you that in a moment and that specifies a particular data type and you're not allowed to put in anything else it sets the data type for you but you will use this one here a data type that specifies the return data type if this argument is omitted the dialog box returns text all right that's all syntax out of the way let's go in and start putting in a couple of message boxes creating a login form that you might be able to use first of all I'm going to here's the message box function we're not going to specify a data type we're just going to use the function you'll notice in this code we've just got add 9 equals input box it is the function that we are dealing with here so let's put this into some code and see what happens now this is just a little bit more complex and then what I've done here is we've set two variables one called add data which is just picking up the last available cell in the row and in this instance it's Row 1 so we can just keep dropping data one in after the other the second is our function here's the variable add name you're familiar with this with is it two times now and then we're saying if ad name equals empty then exits up so in other words we don't want to if there's nothing in there we want to get out straight away and then we're adding the data value equals ad name and they're offsetting it and putting in now so let's see what happens when we do that I'll remove this piece of data out of here so let's run through this and see how it's going to work we put in here the user and the time so we're clicking to here hit the f5 key and it says type your name will type in Trevor I'm logging in you see and now it's saying Trevor and I logged in at this date and time run it again f5 and the same type your name Mary hit the enter key Mary logged in at the same time I will do it again f5 key and Fred and then it goes so you see we're creating a login in a procedure here now let's take that just a little bit further what we want to do now is add a case statement to it so we can validate whether the name is Trevor Harry or Mary and if we don't meet either of those three criteria then we're going to have a message box to say you're not authorized and we're going to exit the sub so we're using the message but we're using the input box I should say in order to get the data and then we're checking the data against what is in the case statement and if the expression is met the statement meets that expression then we are running a piece of code so let's have a look at that in our procedure so we'll put our cursor inside hit the f5 key it says type in your name and we're going to put in Bill and click OK and it saying you're not authorized we run it again the f5 key and this time we'll put in Trevor click OK and the data is added and we're saying welcome Trevor a little message box that's picking up that variable and adding it to a message box so we're using the input box to get the data checking it with a case statement and then messagebox to validate that to the user that everything went fine now in that last example you notice that it was getting quite long the piece of code and really we had three names what we really would want if we had a little organization that we're running we'd want maybe 20 or 30 names to be able to validate to get into our our workbook whatever it is so what we need to do is put that information into a range and then run a loop that loops through the information checks to see if the name is there and if the name is there that we put in the input box well then we're going to run our procedure if it's not there then we're going to exit and say you're not authorized here's the code here you can copy it over to be on the website and be quite a bit of code that you can use but what this is going to do is smarten up the code and we can now put into here twelve pieces of art in fact you can put a hundred in if you wanted so here's the range of names over here d2 to d4 teen now there's the range there if you wanted to add more names you just expand that range in there this is working off the active worksheet now it's just simply setting up a little for each loop it goes through loops through all those names and checks to see if that name is what you put in the input box if it's in there then it's going to let the user login but if it's not in there it's going to send a message saying you can't login and for the first time here we're using application see that there is one application input box application now turns this from a function to a method from a function to a method and type two is type four text so let's run this piece of code first and then I'll take you over and show you the different types that you can use data types so put our cursor in hit the f5 key to run it here's our input box it says add a name or we're going to say Betty and click OK and it's saying you're not authorized because Betty is not in our list but let's try doing it again with one that's on our list f-five and we'll use chip woozi of chips in okay welcome and we added that he was there and the time that he was added to as she that he logged in to our workbook so that's how you would use the messy or the input box along with a message box and the if statement and a loop in order to set up a login form very simple one I know that and we can use a user form to do a more complex one but I thought this is a great opportunity to showcase what you can use the input box for and this code will be up on the website let's just go over and spend a moment and have a look at those data types so here they all are remember by adding the application before the keyword input box we're turning it into a method and here are the types that are available zero for a formula one for a number two for a string that's what we use for for a logical value that a boolean true or false eight for a cell reference I'll show you how to use that in just a moment sixteen for an hour and 64 for an array of values they're the types and this is wonderful because it stops the user entering wrong data so just before we finish this tutorial I want to show you how to use a cell reference in the input box because you will use that a lot so let's move over this is just a small procedure that uses the input box allows you to scroll over an area on your worksheet we're using data type eight and then we're setting the print area and then opening your print dialog box now there's something that you need to notice I put a fresh sheet up here we'll be working again with the active sheet let's have a look at this code because it's a little bit different we've got an error handle it there because if you hit the cancel out on this it's going to go to the debug mode and you don't want that so we just put a little error handler in on error resume next and then we're resetting it down here on error go to zero now in front of our variable for print area we have put the set which means that we want to pick up the cell reference is not the values we want the cell references the address is not the value so when we run the we're going to be able to scroll over the area I'll run it and show to you hit f5 it says scroll over the print area so let's do that let's see if we can get it in here on a yellow or just scroll over the print area and then click ok the print dialog box now comes up and we can just print that but what we want to do we don't want to print it we want to check whether we've actually picked up that yellow area so that's how we're going to preview excellent all we've got is just the yellow area to print let's go back now run it again and this time we're going to scroll over the green area here set the print area for that all right let's have a look at the preview we've just got the green area works perfectly now I just use that because I wanted to show you how you can use the input function and the input method I should say to be able to pick up a range of data if you left the set feature out you'd actually get the values that were in that range this is true from online PC learning there's some information about the input box and the function and the method but if you go to the website the article will be up shortly it'll give you more comprehensive information than you need and there'll be a lot of VBA examples up there there'll be a lot of examples up there that you can take and use in your applications thank you once again for listening and bye for now

21 Replies to “VBA for Beginners – VBA Input Box”

  1. MOHAMAD SIDDEEK

    Hi, indeed!I have learned much cording system in Excel VBA by your support.please help me to get calculate from top to bottom mostly I found left to right, this is like a day book in a day book if we enter few journal it should give total in a below row again enter that total has to go below row and should give total and this total only has to next sheet how can solve this matter by excel VBA. please don't ignore this question. God bless you and thanks in advance,

  2. Ronnie

    You are the best and lovely person I've ever see online with great love to help others to success. You are too much that I have no words to describe what I think about you. I'm starting my excel learning trip and I know you will always be with me on the road. Thanks. Ronnie

  3. Keith Bentley

    Hi Trevor
    I am trying to create this code, but I am getting no further than Set AddData as I get Run-time error '1004' Application-defined or object-defined error. Code is listed as below: Thanks for any help
    Sub Login()
    Dim AddData As Range
    Dim AddName As String
    Set AddData = Cells(Rows.Count, 1).End(x1Up).Offset(1, 0)
    AddName = InputBox("Type Your First Name", "Add Name")
    If AddName = Empty Then Exit Sub
    AddData.Value = AddName
    AddData.Offset(0, 1).Value = Now

    End Sub

  4. Michael Ross

    Thank you for these videos, I am watching and going along with you.  I am having a problem with the code.  I followed exactly what you have and I get "Object variable or with block variable not set".  I hit Debug and it highlights this "AddData.Value = Nme.  I am very new to this but you're making it very easy to learn and this seems right.  I am listing the entire code below.  Please help.

    Sub Login3()
    Dim AddData As Range
    Dim Nme As String
    Set AddDatat = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    Nme = Application.InputBox("Add Name", Type:=2)
    Auth = 0
    For Each cName In Range("D2:D14")
    If cName.Text = Nme Then
    AddData.Value = Nme
    AddData.Offset(0, 1).Value = Now
    MsgBox "Welcome:-" & Nme
    Auth = 1
    Exit For
    End If
    Next cName
    If Auth = 0 Then MsgBox "You are not authorixed"
       

    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *