VBA : Input Box<\/p><\/div><\/p>\n
Let’s take another example where you want user to enter a valid Cell Range to perform some operation on that range.<\/p>\n
\nSub ClearSelectedArea()\n Dim rng As Range\n Dim strAddress As String\n strAddress = InputBox(\"Select the cells to be cleared\")\n Set rng = Range(strAddress)\n rng.Select\n Selection.ClearContents\nEnd Sub<\/code><\/pre>\nWhen InputBox dialog Box opens then your Excel Workbook get frozen. It means you can not select any cell or do anything until you closes the InputBox dialog box. It means this kind of InputBox is good for users to enter any text or number. But in the above example if you see, It is difficult to Input a range Address in input box without having flexibility to see and select the cells or cell-range. Without doing so you will not be able to select.<\/p>\n
This is where the other InputBox comes in to picture. Technically the InputBox which we have used before is called as “VBA InputBox Function” and the other InputBox (which I am calling it as Smart InputBox) is an InputBox method in Excel VBA.<\/p>\n
Since this Smart InputBox is a method in Excel VBA, therefore, it must be used like this: \n \n rng = Application.InputBox(“Select the Cells to be cleared”)<\/span><\/strong> \n \nFrom the above code, it looks very much similar to what we have used in normal InputBox but there are few differences in the functionality.<\/p>\nAs I told smart InputBox is a VBA method, it means It is not necessary that it will return only string. It may return different types of values like Boolean type, Range Object or a number. This InputBox method can return different types of values, then there must be an identifier which tells user which type of value it should return.<\/p>\n
InputBox Method<\/p><\/div>\n
In the above picture the Type<\/strong> parameter is to determine the type of the return value. For different types of values, there are different types which you need to mention. \nFor example, in above case when you want that a user has to select a range address then you need to pass the Type Parameter as 8.<\/p>\nBelow table shows the different Type Values and corresponding data types: \n<\/p>\n
\n\n\nType Value<\/td>\n Type Meaning<\/td>\n<\/tr>\n \n0<\/td>\n A formula<\/td>\n<\/tr>\n \n1<\/td>\n A number<\/td>\n<\/tr>\n \n2<\/td>\n Text (a string)<\/td>\n<\/tr>\n \n4<\/td>\n A logical value (True or False)<\/td>\n<\/tr>\n \n8<\/td>\n A cell reference, as a Range object<\/td>\n<\/tr>\n \n16<\/td>\n An error value, such as #N\/A<\/td>\n<\/tr>\n \n64<\/td>\n An array of values<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n
Therefore in the above example we can use the InputBox method to take the range object from the user and we need to pass the type parameter as 8.<\/p>\n
Sub ClearSelectedArea()\n Dim rng As Range\n Dim strAddress As String\n strAddress = Application.InputBox(Prompt:=\"Select the cells to be cleared\",Type:=8)\n Set rng = Range(strAddress)\n rng.Select\n Selection.ClearContents\nEnd Sub<\/code><\/pre>\n <\/p>\n
InputBox Method looks little different from the InputBox function as show below:<\/p>\n
InputBox-Method<\/p><\/div>\n
Now in the above example you can see that InputBox is accepting only valid excel range. Even if you try to enter an invalid Range Input then it will throw an error. This is the reason I prefer calling it as Smart InputBox because it does the validation before accepting any value entered by the user. <\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"The most easiest and commonly used function in VBA is Input Box. Without much VBA coding, user can be asked to enter some input at any point of programming. To illustrate the simplicity of Input Box we will take an example. Example: You are trying to add a New WorkSheet in a Workbook but the […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1673,1678,5204],"tags":[],"class_list":["post-12172","post","type-post","status-publish","format-standard","hentry","category-excel-functions","category-interesting-vba-functions","category-vba-programming"],"yoast_head":"\n
Input Box and "Smart" Input Box - Let's excel in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n