Cannot Run Inputbox Type 8 More Than Once In A Procedure

Aug 16, 2006

I cannot run the Type 8 inputbox (for user specified ranges) more than once in my procedure.

In the code below, the first inputbox for rngSource works fine, but clicking the ok button after selecting a range for the second inputbox (rngDest) pops up the following error:

Run-time error '424':

Object required

I initially thought that the Default option was to blame. So I created a dummy procedure with the command for the first inputbox repeated twice, and got the same error.

Sub InputBox_For_Ranges()
Dim rngSource As Range, rngDest As Range, rngStartingPoint As Range

Set rngStartingPoint = ActiveCell

Set rngSource = Application.InputBox("Select Source Cell", _
Type:=8, Default:=rngStartingPoint.Address)

Set rngDest = Application.InputBox _
(prompt:="Select Destination (SINGLE CELL ONLY)", _
Type:=8, Default:=rngSource.Offset(0, 1).Address)
End Sub

View 6 Replies


ADVERTISEMENT

Type Mismatch Error On InputBox

Oct 16, 2009

I am trying to only have the option 1, 2, or 3 be entered. I have that part down, but if someone clicks cancel or just closes the input box i get the type mismatch I believe because no integer was found....is there a way around this?

Dim intResponse As Integer
intResponse = InputBox("Choose One" & Chr(10) & Chr(10) & "1 - Lease Lock" & Chr(10) & "2 - Upgrade" & Chr(10) & "3 - Lease Lock & Upgrade", "Contract(s) sent out")
If inResponse <> 1 Or strResponse <> 2 Or strResponse <> 3 Then
MsgBox "Must choose (1,2,3)"
Exit Sub
End If

View 2 Replies View Related

Inputbox Button Control + Msgbox For Empty Inputbox

Jun 30, 2009

I have the inputbox so i can set a string value,
When the inputbox Cancle button is pressed i want to exit sub,
If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname.
If the inputbox has a value do >>>>>>That>>>>>

View 6 Replies View Related

VBA Functions: Type # For Date (Type:=?)

Oct 22, 2008

1. Is there a VBA Function equivalent to the FIND() function, If so What is it?
2. Let's say Im Putting a Date into a inputbox, what is the type # for date (Type:=?)??

View 2 Replies View Related

VBA Inputbox

Sep 25, 2008

I have another dilema. I am writing a macro that essentially takes a template from a workbook and copies it to another workbook that already has data. the data is then imputed into the pasted template through the macro... my dilema is that I have numerous data sheets so at the begining of the code I need the macro to pause so the user can manually select the cell or range that the copied template is to be pasted too... I was thinking an inputbox would work but I am unsure how to write this part of the code.

View 3 Replies View Related

VBA Inputbox To Sql Queries

Jul 31, 2007

The report is a "template" that will be ran once a month every month and will look back at the previous month's data. What i've invisioned is writing a vb script to load on workbook open asking various questions via "inputbox()" method.

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '20070731 10:10:51.450'
SET @EndDate = '20070731 10:11:51.450'
SET ROWCOUNT 100
SET NOCOUNT OFF
SELECT EventTagName = EventHistory.TagName, Value
FROM EventHistory
INNER JOIN AnalogSnapshot ON EventHistory.EventLogKey = AnalogSnapshot.EventLogKey
INNER JOIN SnapshotTag ON SnapshotTag.SnapshotTagKey = AnalogSnapshot.SnapshotTagKey
WHERE SnapshotTag.TagName IN ('SysSpaceMain')
AND DateTime >= @StartDate
AND DateTime

View 9 Replies View Related

InputBox In Macro

Dec 30, 2008

I have a macro that sends data from excel using MS Outlook. I want to add a more specific Subject header in outlook, and do this using an input box.

I thought adding something like this would work, but it doesn't... (below are just extracts from a very long code...)

Dim CurMonth as String

CurMonth = InputBox("Please enter the reporting month","")

.Subject = "Trial Balance - All companies" & CurMonth

When outlook generates the email, all I get in the subject line is "Trial Balance - All companies"

View 9 Replies View Related

Create VBA InputBox

Jun 4, 2009

I am trying to create an inputbox using code from this site:

Sub NumericDataType()
Dim lNum As Long
On Error Resume Next
Application.DisplayAlerts = False
lNum = Application.InputBox _
(Prompt:="Please enter you age.", _
Title:="HOW OLD ARE YOU", Type:=1)
On Error Goto 0
Application.DisplayAlerts = True
If lNum = 0 Then
Exit Sub
Else
MsgBox "You are " & lNum & " years old."
End If
End Sub

I get an error on the first line that says, "Compile error: argument not optional".

View 4 Replies View Related

Two Option InputBox

Mar 22, 2007

I am trying to simply the data input for someone who is not very experienced.

I have had some success but need to have an extra option in case of mistakes.

Ideally I need the following -

I want an inputbox that will appear when the user starts the macro and for example asks Enter Surname

Then I know what I want but I don't know how to do it.

I want a box to display the Surname that the user typed into the inputbox with a message asking Are Details Correct? with a Yes and No Button.

Clicking the Yes button moves onto the next inputbox clicking the No button cancels the whole macro before it enters data into cells.

View 9 Replies View Related

How To Set Outside Procedure

Apr 21, 2009

I have the following codes used with a user form, but they are all not working due to an outside procedure problem,

View 14 Replies View Related

InputBox - Exit Sub When Cancelled

Aug 30, 2012

How to use Exit Sub when I push Cancel with InputBox. Heres my code

VB:
Dim fName As String
Dim strMessage As String
strMessage = "Select .csv File"

MsgBox strMessage

[Code] ....

Its working at the moment when I push cancel, but when I select a csv file to import it comes up with an incompatible error.

View 2 Replies View Related

Inputbox Integer Verification

Feb 21, 2013

In the following code snippet I have worked out a way to verify that the user inputs an Integer and then prompts downstream if an improper value was entered. The user is prompted for a corrected reponse and loops until they get it right. The initial Cancel at the first prompt was causing issues until i saw poster: titarelli use StrPtr(). Except for subsequent cancels, the code works but it is clunky at best. How to tighten this up?

VB:

Dim Quantity As Variant
Dim CorrectedQuantity As Variant
Dim j As Integer

j = 194
Quantity = 0
CorrectedQuantity = 0

[Code] ......

View 2 Replies View Related

Select Sheet Using Inputbox?

Jun 5, 2014

i have a close workbook with path ("Z:42766Jan 2 Dec 2014Tally.ERP9GrpSum.xls") from another open workbook i want to popup a inputbox to select the sheet and run the macro on selected sheet.

e.g
i put 2 in inputbox than run the macro on sheet2

View 2 Replies View Related

Date Inputbox Value Correction

Feb 9, 2014

It show error, how to correct it .

[Code] ......

View 1 Replies View Related

Inputbox For Simple Verification

Jun 3, 2008

I have a VBA userform with various textbox fields like 'Name' and 'Address' and 'Amount'
I would like an input box to pop up that asks the user to 're-enter for verification'
So that they have to type the same thing twice, to protect against typos

How do I code it so the program compares the inputbox to the textbox and passes only if they are identical?

(or even, using two inputboxes instead if that would be easier)

View 13 Replies View Related

Get Default Value From Cell For Inputbox

Oct 2, 2008

I am working on a production spreadsheet, and I'm trying to figure out the easiest way for my coworkers to enter data into the system. Each shift, we produce from 5-20 different varieties of chips, and we keep track of how much we've made of each. I've decided that a simple ADD and DEL button at the end of each row will work (unless anyone can think of something easier or better, let me know pls).

Cell G3:G30 is where data will be stored for each variety. The default value for each variety is stored in column C. I found a very basic macro that adds a value to a selected cell, but I wish for it to find the default quantity for the variety in that row (changes day to day as per our schedule), and add it to the value in column G. The DEL button will do just the opposite.

I have 3 worksheets that I need this macro in as well (days, afternoons, midnights), but I don't think that will be any problem. I've searched around for a solution, to no avail.

View 6 Replies View Related

InputBox To Edit Data

Oct 2, 2008

I have a worksheet, attached, that is a supplier response to a purchase order. Column E contains the original qty ordered of each part number, Column H contains the original price. Now, I've created a quick macro, (in the workbook), that copies the original values to the confirmed values, and the formulas in columns G and J calculate the qty backordered, or flags the part as a price change.

After copying the original PO values to the confirmed values, the supplier would need to go in and make whatever changes are necessary. For example, we ordered 3 and they shipped 0 of a certain part, or the price has changed.

What I would like to do is create a macro that pops up an input box where the supplier enters the Keystone part number, (column A), then enters the new quantity or new price, and those items are automatically changed on the response form. I picture it in the form of 3 input boxes, in the first box the buyer enters a part number. A second box pops up asking if it's a qty change or price change. They would enter the value in a 3rd box, and that value would automatically be changed on the response.

View 5 Replies View Related

Referencing A Sheet From An Inputbox

Oct 14, 2008

I have a quick question that'll hopefully have a quick answer. Part of my workbook requires an input box to appear. When you type the name of one of the worksheets into the box and hit enter I want a VLOOKUP formula I've written to compare to that worksheet. Heres what I mean, with some descriptions of what i would like:

View 8 Replies View Related

Manipulate Inputbox Text

Jun 3, 2009

I have a macro that takes input from the user and replaces certain text on several worksheets. One of the inputs is a username in the format of firstname.lastname. I need to manipulate this input such that the dot is removed and the first twelve characters only are used, all in upper case. e.g. Michael.Jackson would become MICHAELJACKS

I know how to use cells.replace to replace the text but I don't know how to use a formula to manipulate the inputbox text before I do the replace.

View 4 Replies View Related

InputBox User Validation

Jun 16, 2009

InputBox User Validation. Need to modify my code as follows?

View 5 Replies View Related

CInt() Conversion & Inputbox

Jul 2, 2009

as i understand CInt() converts STRING variables to INTEGER variables. I am trying to create an inputbox, with input validation. So, if the user types in an integer, it will continue to a msgbox; if they type a number in string format, as in 'two', it will convert this into an integer and again display it in a msgbox; if they press nothing a msgbox will appear telling them there is no input; and if they press cancel the programme closes.

View 4 Replies View Related

Getting All Days Of Month From Inputbox?

Aug 13, 2009

Need a way to get/calculate ALL of the days of the months from a date range entered into an inputbox in which a user may enter any day of a month? In other words, the user enters 9/14/2009 in the first inputbox then 10/8/2012 in the second and code would calculate the number of days as 9/1/2009 - 10/31/2012.

Here is the current code I'm using that calculates only the exact days:

View 3 Replies View Related

Input Date Without Using An InputBox

Sep 14, 2009

I have a VBA code that works really well but i wondering if there is a way to do the same thing but without the message box and just enter in the dates instead.

View 11 Replies View Related

Can Inputbox Have Selections For User?

Sep 18, 2009

I have not used Inputboxes or Userforms much. I have read through some of the messages here, but I need to learn more about them. I need to create some type of user interface, where the user would be asked to select between two choices. Based on the choice made, a macro would be executed. The two choices run different macros.
I need direction or an example of an Inputbox that shows two choices instead of a blank input space.

View 3 Replies View Related

Manipulating InputBox Variable

Oct 20, 2009

I have two inputboxes. I want to set the default value of the second inputbox as (effectively) one cell down from the first inputbox entry. In other words, if I type G17 into the first inputbox, I want the second inputbox to then have a default value of G18.

View 5 Replies View Related

Inputbox To Open A File

Dec 18, 2009

I have workbook and when I open it I'd like to be able to open another workbook and copy the data into a new sheet in original workbook. The file I'd like to open is always "123_PPS.CSV". I'd like to enter the number (123) into a msg box and then have that file open or copy into sheet2 so that I can get info from it into the cells in the packing slip.

View 14 Replies View Related

Bypass Inputbox In 20 Seconds?

Apr 4, 2012

I am working on a script that allows a user to enter a number (days), and an automatic process performs based on that number. The inputbox has a default value. I have everything except for one issue--we would like the default value to be accepted and the inputbox close if the user does not click OK in say 20 seconds (in order to keep the process from tying up the rest of the process). Is there a way in VBA to click "OK" in the inputbox in 20 seconds so the process can run if the user doesn't click it himself?

View 5 Replies View Related

How To Get Inputbox Name A Shape Within Workbook

Apr 11, 2012

I am trying to get an inputbox name a shape within my workbook. This is what I have so far and i am getting a Run-time error ("The specific value is out of range") on the part in red.

Code:
Dim MyInput
Sheets("List Form").Select
ActiveSheet.Shapes.Range(Array("BlankTestButton")).Select
Selection.Copy
Sheets("For Copy").Select
Range("A36").Select
ActiveSheet.Paste

[code].....

View 3 Replies View Related

Default Inputbox Value Date

May 27, 2013

I made a selection and coloring macro, the filter is based on the inputbox that pops up. What I would like to do here is, that when the box pops up it would have a written date in it, which is the today always. And the filter should select all which is 2 weeks later than current date.

Code:
Sub kethet()
Dim datum As Long
Dim most As String
LRow = Range("E" & Rows.Count).End(xlUp).Row

[Code] ..........

View 5 Replies View Related

Set Column With InputBox Entry

May 5, 2014

I want to set the column to search using Find in second code below.

Can't get the code to cooperate even though myCol does properly reflect the column number.

And this little tester fails as well...?

Code:
Sub Booger()
Dim myCol As Long

myCol = InputBox("Enter a column number")

With Sheets("Sheet2")
.Range(Cells(1, myCol), Cells(25, myCol)).Select
End With
End Sub

Here is the segment I am trying to make work in the main code

Code:
With Sheets("Sheet2").Range(Cells(1, myCol), Cells(25, myCol))

Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _

[Code] .......

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved