Input Box For Numbers And Dates

[ShredDude]

I'm new to VB coding.

I'm having trouble capturing numbers and dates with input boxes. I've got text strings working fine.

The code below prompts user to input four key variables to a template.

I've tried various formats for the variables "Price" and "closeddate" but this codealways fails when you enter a price (3rd Input Box)

I read help about TYPE codes in the Input Box Application vs. Input Box method that this uses, but I'm missing something.

Could someone please point me in the right direction on that.

Also, if someone could point me to a reference on how to handle the user clicking cancel in the input box...

(The variable "newsheetname" is defined previously in this macro's code. It's working fine for the first two Input Boxes.)

Thanks in advance.

Dim seller As String
Dim Buyer As String
Dim Price As String
Dim closedate As String


    seller = InputBox("Enter Seller's Name", "Seller's", "Type Seller's Name Here")
    Worksheets(newsheetname).Range("D7").Value = seller
    Buyer = InputBox("Enter Buyer's Name", "Buyer's", "Type Buyer's Name Here")
    Worksheets(newsheetname).Range("i7").Value = Buyer
    Price = InputBox("Enter Contract's Purchase Price", "Purchase Price", "Enter Purchase Price Here")
    Worksheets(newsheetname).Range ("i11"), Value = Price
    closedate = InputBox("Enter Contract's Proposed Closing Date", "Close Date", "Enter Prposed Closing Date Here")
    Worksheets(newsheetname).Range ("i9"), Value = closedate

[rylo]

Hi

1) you have a comma instead of a full stop in the code

Worksheets(newsheetname).Range ("i11"), Value = Price

should be

Worksheets(newsheetname).Range ("i11").Value = Price

and

Worksheets(newsheetname).Range ("i9"), Value = closedate

should be

Worksheets(newsheetname).Range ("i9").Value = closedate

2) The output from a textbox will be text. If you want the price to be a number, then you will have to convert using VAL

Worksheets(newsheetname).Range ("i11").Value = val(Price)

3) What do you want to do if the user cancels out from the input box? Force them to continue until it is completed?


rylo

[ShredDude]

Rylo:

Thanks for catching my typo. I didn't know about VAL. So its OK to DIM the variables as STRING and then use VAL to paste it into the cell as something else? I"ll give it a try, and try to read some help files on VAL.

If the user hits cancel, I"d want to force them to complete in the dialog that is renaming the worksheet, but for some of the others I could be lienient and allow them to continue on, letting the code paste nothing into that particular cell. The Cells in question on the sheet are open to the user to type into anyway. I was just trying to make it easy for some novice users.

Thanks for your reply. I'll go pay more attention to my syntax.

While we're at it...any tips on making things run faster. This macro is tied to a Command Button that triggers it off. Any ideas?

I've got another macro that seems to go way too slowly also. It does what I ask of it, but I'd think on the machine I'm on it should crank. MY CPU meters get redlined on both processors as it does its deal. The code does nothing more than paste some values into a column. Each cell in that column however drives into formulas in six adjacent cells that each do some retrieval from other pages with INDIRECT references and then some simple math. I inserted an

Application.ScreenUpdating= False

at the front end and then turn it back to True once the looping is done. I'm not sure if that made it faster, but at least you don't watch it sporadically post data into their respective fields. It does its deal, and then when it's done the whole screen appears. I like that, but I'm hoping to make the whole thing occur more quickly.

It begins with a Worksheets.Activate so I'm wondeirng if each time it pastes a cell, it's in essence starting itself over again so I get a geometric progression of complexity as the code does its thing. Or maybe its just the adjacent formulas having to do their thing with each update that slows it down? Is there a way to turn off recalculating until you're done pasting in the values that will trigger recalcs? that might be fun to try...

I don't know. I'm just a newbie trying my hand at a few things.

Any insight much appreciated.

Thanks.

[rylo]

Hi

1) Here's one way to force the users to enter data.

Do
    seller = InputBox("Enter Seller's Name", "Seller's", "Type Seller's Name Here")
    Loop Until Len(seller) > 0
    Worksheets(newsheetname).Range("D7").Value = seller
    Do
    Buyer = InputBox("Enter Buyer's Name", "Buyer's", "Type Buyer's Name Here")
    Loop Until Len(Buyer) > 0
    Worksheets(newsheetname).Range("i7").Value = Buyer
    Do
    Price = InputBox("Enter Contract's Purchase Price", "Purchase Price", "Enter Purchase Price Here")
    Loop Until Len(Price) > 0
    Worksheets(newsheetname).Range("i11").Value = Price
    Do
    closedate = InputBox("Enter Contract's Proposed Closing Date", "Close Date", "Enter Prposed Closing Date Here")
    Loop Until Len(closedate) > 0
    Worksheets(newsheetname).Range("i9").Value = closedate

It's pretty crude, and doesn't give any warning about blank, just forces the input box up again until it is completed. Does the same thing for a cancel. You could expand it to make sure the result is not the same as the default as well.

2) Maybe turn off the calculation, and turn it on again when all the data has been added. Also, if there are any events, turn them off, and on again. Also, do you select the cells before pasting? If so, then modify so the result goes to a cell, rather than selecting first.

Without seeing what you have, and you code, bit hard to make pertinant suggestions.


rylo

[ShredDude]

Thanks again for your replies. I inserted the Do Loop stuff around the one with price, increased the LEN test to >5 since all prices will be six or seven figures and went from there. I'll play with the other edge conditions and see if I can make it right. Thanks for the lead.

the other code I was referring to now screams. I rebooted my system, and it works in a blink of the eye. Not sure what was so slow before. Here it is:

Private Sub Worksheet_Activate()
Dim wscount As Long
Dim i As Long
wscount = Worksheets.Count - 1
Worksheets("Escrow Summary Sheet").Range("d8:d18").ClearContents
Application.ScreenUpdating = False
For i = 3 To wscount
    Range("D" & i + 5) = Sheets(i).Name
Next
Application.ScreenUpdating = True
End Sub

To your question about selecting the cells first, I don't think this does that. I take it that's something to avoid?

Just curious...I tried to do the math of reducing the worksheet count by one in the For statment and I never got it to work. So I created a variable, did the math elsewhere and then used the variable name in the For statement. Is that the way you have to do soemthing like that? Seems like a waste. Did I miss something?

[rylo]

Hi

1) Yes, you don't select the cell, so that is OK. Good to avoid selection of a cell as it slows down the processing.

2) You should be able to do a loop like

For i = 3 To worksheets.count - 1
    Range("D" & i + 5) = Sheets(i).Name
Next

Can't see why it would have failed for you.

rylo