Simple Macro ? : Input Box or other....

[GUIDO22]

Hi All
I wish to automate a repetitve task using Macros. I am not new to Excel (Access is my area of expertise), but I am new to using Macros in Excel.

The macro I require is simple - I wish the user to press a button - an inputbox or similar appears and prompt user for a cell value, on OK this is entered in a specific cell, the box reappears half a dozen or so more times askig for a single value each time - each value is written to its respective cells - the row row from which the Macro was initiated is the actual row the values are written to.
This is to help a user tabulate a reasonable amount of data instead of wroking through column by column / row by row....
Hoping you can help.

Thanks for any assistance you can give.
G

 

[NickHa]

I'm not clear on the question you are asking? Is it:
1. How to place a button on a worksheet?
2. How to show an input box from the button?
3. How to save the user input to a specified cell?

Do you want separate buttons for each row, or do you want to use the same button for the series of inputs?

A simple method to get user input for a specific cell is to use the worksheet BeforeRightClick event:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "Cell is " & Target.Address(True, True, xlA1)
Rem you could open a UserForm here to get the input
usfInputForm.Show
Rem you can save the value using Target
Target = "ABC"
Rem you could use the Target coordinates to determine the exact contents to be used
Select Case Target.Column
Case 1 ' do what's required for column 1
Case 2 ' do what's required for column 2
Case 3 ' do what's required for column 3
Case Else
End Select
Rem Suppress the Excel right-click menu
Cancel = True
End Sub

'Target' is the cell in which the right-click is detected. Setting cancel suppresses the default right-click selection.

If it's a button on the worksheet you want, then use the Developer tab in the Ribbon, select Insert from the Controls group and choose the Button icon (top left). That will prompt you with the macro name to be assigned. The Developer tab doesn't show in the Ribbon by default - you need to set it in the Excep Options from the Popular tab.

If you need a user inpout box, go to the VBA editor by <alt>F11 and insert UserForm. You design this in much he same way as you design a form in Access. You can right-click in the form and view the code.

If you can clarify your question further, I can give more specific help, but in the meantime, I hope this is useful.

 

[GUIDO22]

OK - I have a spreadsheet for input of dimensional data for engineered parts. The right most columns in each row have formulae to do the necessary calcs on the figures that have been input.
For example : I have cells C1 , C2 , C3 that require input of a number

I need a button which I will invoke the macro to :
- Prompts the user for a numeric (type Single) value - user inputs , press OK and value is written to the cell C1
- Prompts the user for a numeric (type Single) value - user inputs , press OK and value is written to the cell C2
- Prompts the user for a numeric (type Single) value - user inputs , press OK and value is written to the cell C3

When all three have been added - stop.

Comprends?
Thank you

 

[NickHa]

I have attached a sample file which does what you describe. This is compatible with Excel 2007 and 2010.

There is a command button on the worksheet, which opens a user form with data entry textboxes for three fields, starting with the active cell on the worksheet.

I have also included two buttons in the Ribbon on a tab named "Custom". The "input" button does exactly the same as the worksheet button. The "clear" button clears the contents from the input range.

I have set the user form up with an 'OK' button, which
a. transfers the content from form textboxes to worksheet cells
b. moves to the next row
c. clears the user form content ready for next set of data

The process ends when the user click Cancel.

You need to add code to validate the user input where indicated in the comments (within the user form code). I have not included any error-handling code.

You should be able to modify this to suit your needs. If anything is not clear, please advise.

 

[GUIDO22]

Thank you Nick - pretty much what I needed .... sufficient here for me to now put a little more meat on the bones! Thanks again, great help.

 

[NickHa]

You're very welcome.