Make Macro input data into Excel spreadsheet?

Question

I have never used macros before and I need to create a macro that asks the user for the following inputs: Date, Project#, Fault, Problem, and Solution. Once the user enters this data I want it to go into an Excel spreadsheet in columns labeled with the titles above. If someone could help me out I would appreciate it very much.

Answer 1

Hi Rob, the subroutine coded in the other answer should work. I would change several things about it however. The first is On Error Resume Next. This effectively hides errors from the user, which is appropriate in some circumstances (mostly when the code isn't well written and you are actually "ok" with some errors, but want the code to keep going to the next step). However in most cases you DO want your user to be aware that an error occurred, so he/she does not assume that all is well with entered data. Better they come to you saying "I got this error", rather than them never realizing they failed to complete the process.



So to accomplish this, I've removed On Error Resume Next, as well as added a msgbox at the end. This way there is no doubt - if they don't see the msgbox, then the process did not occur as planned. And the msgbox doesn't pop up until the process is complete, and the workbook has been saved...



Secondly, do you prefer to have a worksheet with a name that's meaningful to you in some way? If so, then use this sub. It will be named as Project Data Recorded 06.01.09.3.18 (month, day, year, hour, minute). We can adjust this if you need, email me.

Along these same lines, won't you need this same type of macro not just for the first time (a sheet is added), but later on to append additional information to an already-existing sheet? If so, we should write one comprehensive set of code that will handel both situations equally (email me or add detail to your question).



Thirdly, in your vba code avoid at all costs using "activesheet". This is because VBA may not agree with you on which sheet is active, this can have very unpredictable results. Better dimension your sheet with a variable name. Hence, I've added this feature as well.



email is [e-mail] if you need adjustments.



and finally, since stupid yahoo answers always has issues with lines wider than a few characters, it's possible what I'm about to post may have some lines cut off. And the geniuses who designed it don't let me upload a text file :) If you get an error running this, email me.



Sub InputSheet()



dim dDate as date

dim sProject as string

dim sFault as string

dim sProblem as string

dim sSolution as string



ddate = inputbox("Please provide date:","PROJECT INFORMATION")

sProject = inputbox("Please provide project name:","PROJECT INFORMATION")

sFault = inputbox("Please provide fault name:","PROJECT INFORMATION")

sproblem = inputbox("Please provide problem type:","PROJECT INFORMATION")

sSolution = inputbox("Please provide solution description:","PROJECT INFORMATION")



Dim MyNewSheet As Worksheet

Set MyNewSheet = Sheets.Add(after:=Sheets(Worksheets.Count))

Dim strname As String

strname = "Project Data Recorded "&Format(Date, "mm.dd.yyyy.hh.mm")



sheets(strname).select

cells(1,1).value = "DATE"

cells(1,2).value = "PROJECT"

cells(1,3).value = "FAULT"

cells(1,4).value = "PROBLEM"

cells(1,5).value = "SOLUTION"

cells(2,1).value = dDate

cells(2,2).value =sProject

cells(2,3).value =sFault

cells(2,4).value =sProblem

cells(2,5).value =sSolution

thisworkbook.save



msgbox "Process is Complete!"



End sub

Answer 2

Excel Macro Input

Answer 3

They are making it way too complicated. Below is a simple code that would put the entry in Cell A1. The variable the answer is saved to is Answer. However the variable goes bye bye after the subroutine is done unless you declare Answer as Static instead of as a Dim. So you'd declare: Static Answer Below is code to put the InputBox answer immediately into Cell A1. Dim Answer Dim Message As String Message = "Type in the name of the person you want to assign this work to:" Answer = InputBox(Message, "Work Assignment") Range("A1").Value = Answer Granted, that mine doesn't have all of the error handling stuff that there's does. But for something simple, you don't need all of that error handling.

Answer 4

Rob, you could make a form but is more complicated to explain.

What you can do is a macro like this one below and assign it to a button. This isn´t the best way but it works fine.



'@Objective: Update columns names after user input date, project, fault, problem and solution

Sub ColumnNames()

on error resume next

dim dDate as date

dim sProject as string

dim sFault as string

dim sProblem as string

dim sSolution as string



ddate = inputbox("Please provide date:","PROJECT INFORMATION")

sProject = inputbox("Please provide project name:","PROJECT INFORMATION")

sFault = inputbox("Please provide fault name:","PROJECT INFORMATION")

sproblem = inputbox("Please provide problem type:","PROJECT INFORMATION")

sSolution = inputbox("Please provide solution description:","PROJECT INFORMATION")



sheets.add

activesheet.name = sProject

sheets(sproject).select

cells(1,1).value = "DATE"

cells(1,2).value = "PROJECT"

cells(1,3).value = "FAULT"

cells(1,4).value = "PROBLEM"

cells(1,5).value = "SOLUTION"

cells(2,1).value = dDate

cells(2,2).value =sProject

cells(2,3).value =sFault

cells(2,4).value =sProblem

cells(2,5).value =sSolution

End sub