[Craig_EP82]

Hi all,

Total n00b when it comes to writing Macros. The problem is I need a my Macro to select a cell, then bring up an input box. The user enters there acc number in to the box and it is displayed in the active cell with an * at the end. The macro then takes a new line and should loop the input box again until the user enters stop (I'd prefer they just had to click on cancel). Here is my code:

Sub AddAcc()

' Select first cell for data to be entered.
					Sheets("Macro Data").Select
					Range("A6").Select
' Request account number.
Dim Acc As String
Do Until Acc = "Stop"
		Acc = Application.InputBox(Prompt:="Please input the account number.", Title:="Account Number", Default:="Account Number here")
	
		If Acc = "Account Number here" Then
			 
		Exit Sub
		
	
			Else
	
			Select Case Acc
	
				Case "" & Acc & ""
' Input account number in to Active Cell followed by an "*" and take new line.

					ActiveCell.FormulaR1C1 = "" & Acc & "*"
					Application.SendKeys ("{ENTER}")
						   
			End Select

		End If
Loop
		
End Sub

Please note that after the data is input into the active cell, a new line is taken. However with this loop in place, it just inputs the number without taking a new line and doesn't bring the input box back up either.

Any help appreciated!

[Craig_EP82]

I've worked out what is causing the problem. It seems to not like the SendKeys command, as with this removed the Input Box loops. So my new code is now:

Sub AddAcc()
'Macro Record by Craig Sutherland
' Select first cell for data to be entered.
					Sheets("Macro Data").Select
					Range("A6").Select
' Request account number.
Dim Acc As String
Do Until Acc = "0"

		Acc = Application.InputBox(Prompt:="Please input the account number.", Title:="Account Number", Default:="Account Number here")
	
		If Acc = "Account Number here" Then
			 
		Exit Sub
		
			Else
	
			Select Case Acc
	
				Case "" & Acc & ""
' Input account number in to Active Cell followed by an "*" and take new line.

					ActiveCell.FormulaR1C1 = "" & Acc & "*"
						   
			End Select

		End If
Loop
End Sub

So what I now need to know is; How can I make it take a new line before the next acc number is input as I need to list them one under the other?

[cmount]

I'm not positive if I understand exactly what you're doing. Here's what I think you're asking: You want the program to go one cell down every time the loop runs---so basicaly increment the cell by a row change of 1

You'll need to have it take a new line with a rowvariable = rowvariable + 1 inside your loop to go to the next cell

You'll have to initialize rowvariable outside your loop & also make sure you have a Dim & whatever variable name you decide to use to increment row.

I'm going to adapt/mention some code I've written for another project into this situation:

I took Dim shtWhatever As Worksheet ' this is where you'd initialize whatever active sheet you want to be manipulating. I'm not sure off the top of my head what would be the best way to go about having it tell what the active sheet's name is & setting shtWhatever to the Active Sheet. Hopefully you can figure this out.

Dim RowCount As Long


Then inside the loop (I use a for loop in my code), you'll want to use something like:
shtWhatever.Cells(RowCount, [whatever column #]) = Acc & "*"

For example, if you want it to put the text in cell B4, your rowcount would take on a value of 4 (since it's adding it to the 4th row), and the column # would be 2 (because B is the second letter of the alphabet, or the second columm)



I'm not positive what's making your thing fail to loop, but I'd guess it's somewhere in your case handling (the if...else...select case part). I'm not sure if hitting cancel would give a "" output if the text is already "Account Number Here"

Hope that helps....sorry I couldn't be more help

[edit]: Don't feel bad, I'm a n00b too! It just takes some learning before it starts making sense--and even then, sometimes I'll get hung up on a detail

Craig_EP82, on 15 Aug, 2008 - 04:56 AM, said:

Hi all,

Total n00b when it comes to writing Macros. The problem is I need a my Macro to select a cell, then bring up an input box. The user enters there acc number in to the box and it is displayed in the active cell with an * at the end. The macro then takes a new line and should loop the input box again until the user enters stop (I'd prefer they just had to click on cancel). Here is my code:

Sub AddAcc()

' Select first cell for data to be entered.
					Sheets("Macro Data").Select
					Range("A6").Select
' Request account number.
Dim Acc As String
Do Until Acc = "Stop"
		Acc = Application.InputBox(Prompt:="Please input the account number.", Title:="Account Number", Default:="Account Number here")
	
		If Acc = "Account Number here" Then
			 
		Exit Sub
		
	
			Else
	
			Select Case Acc
	
				Case "" & Acc & ""
' Input account number in to Active Cell followed by an "*" and take new line.

					ActiveCell.FormulaR1C1 = "" & Acc & "*"
					Application.SendKeys ("{ENTER}")
						   
			End Select

		End If
Loop
		
End Sub

Please note that after the data is input into the active cell, a new line is taken. However with this loop in place, it just inputs the number without taking a new line and doesn't bring the input box back up either.

Any help appreciated!

This post has been edited by cmount: 15 August 2008 - 07:08 AM

[Craig_EP82]

Thanks for your reply, but I'm a little confused. Could you re-write my code with your modifications so I can do a straight copy paste?

[Craig_EP82]

Please find my most recent code for editing:

Sub AddAcc()
'Macro Record by Craig Sutherland
' Select first cell for data to be entered.
					Sheets("Macro Data").Select
					Range("A6").Select
' Request account number.
Dim Acc As String
Do Until Acc = "Doesn't Matter"

		Acc = Application.InputBox(Prompt:="Please input the account number. If you are finished entering account numbers, please type 'Stop'.", Title:="Account Number", Default:="Account Number here")
	
		If Acc = "Stop" Then
			 
		Exit Sub
		
			Else
	
			Select Case Acc
	
				Case "" & Acc & ""
' Input account number in to Active Cell followed by an "*" and take new line.

					ActiveCell.FormulaR1C1 = "" & Acc & "*"
						   
			End Select

		End If
Loop
End Sub

[cmount]

I'm not entirely clear about the exact workings of your current excel sheet (how you want this thing to work)...or exactly how to implement some of my ideas. Some of them are just ideas on what you might want to do...but I don't know exactly how to do them.

I'll update some of your code to possibly do parts of what you want (based on some assumptions I feel the need to make because I'm not positive what you want)

If you want the macro to always start on cell A6, then your Range("A6").Select will be fine. But if you would want the macro to start on another line ever, you'd have to change to code to take this into account

I'll give it a shot

By the way, you should try to figure out a way to get the cancel button to work =) that would piss some people off a LOT...I did this for you =)

Read the comments I added to the code. Depending on if you want it to find the first blank row after last filled cell in A, you'll need to make some changes

Sub AddAcc()
'Macro Record by Craig Sutherland
' Select first cell for data to be entered.
Dim Acc As String
Dim CurrentRow As Long
Dim CurrSheet As Worksheet
Dim BlankStartRow As Long   ' <--You might use this line if you want the program to find
							' the first row after the last filled row in column A


Sheets("Macro Data").Select
'Range("A6").Select	' I'm not sure if you need this line of code anymore since my CurrentRow
' Request account number.


Set CurrSheet = Sheets("Macro Data")

CurrentRow = "6"   'Assuming your code will always start on row 6 of column A:

'OR you might think about:

'BlankStartRow = CurrSheet.Range("A" & Rows.Count).End(xlUp).Row + 1

'the above code gives you the first blank row after the last filled row in column A
'If your sheet may already have data entered & you don't want your macro to overwrite
'the existing data & instead start w/ the line below the exiting data, you'd
'want to use this code instead of the code for CurrentRow (replace CurrentRow with
'BlankStartRow throughout the rest of this code if you decide to use this instead

Do Until Acc = "Doesn't Matter"

	Acc = Application.InputBox(Prompt:="Please input the account number. If you are finished entering account numbers, please type 'Stop'.", Title:="Account Number", Default:="Account Number here")
	
	If Acc = "Stop" Or Acc = "" Or Acc = "False" Then
			 
		Exit Sub
		
			Else
	
			Select Case Acc
	
				Case "" & Acc & ""	  ' My question:  what is the line to the left doing?
' Input account number in to Active Cell followed by an "*" and take new line.
					CurrSheet.Cells(CurrentRow, 1) = Acc & "*"
					CurrentRow = CurrentRow + 1
						   
			End Select

		End If
Loop


End Sub

Craig_EP82, on 15 Aug, 2008 - 07:49 AM, said:

Please find my most recent code for editing:

Sub AddAcc()
'Macro Record by Craig Sutherland
' Select first cell for data to be entered.
					Sheets("Macro Data").Select
					Range("A6").Select
' Request account number.
Dim Acc As String
Do Until Acc = "Doesn't Matter"

		Acc = Application.InputBox(Prompt:="Please input the account number. If you are finished entering account numbers, please type 'Stop'.", Title:="Account Number", Default:="Account Number here")
	
		If Acc = "Stop" Then
			 
		Exit Sub
		
			Else
	
			Select Case Acc
	
				Case "" & Acc & ""
' Input account number in to Active Cell followed by an "*" and take new line.

					ActiveCell.FormulaR1C1 = "" & Acc & "*"
						   
			End Select

		End If
Loop
End Sub

[Craig_EP82]

Legend. It worked.

[cmount]

Craig_EP82, on 18 Aug, 2008 - 01:09 AM, said:

Legend. It worked.

Did you think about incorporating something like my one line of code I left commented out--to find the first blank cell after a filled cell? The only time my method wouldn't work is if the first blank cell after a filled cell were actually NOT at cell A6. If you have headers or any line in A5, then it will stop at A6. But then again, since you didn't ask for this, I just left that code commented out if you decide to do this.

Otherwise if someone runs the macro, old stuff could end up getting written over without them realizing it for a while...and macros..unfortunately...have no undo!

[cmount]

Craig_EP82, on 18 Aug, 2008 - 01:09 AM, said:

Legend. It worked.

Another helpful tip: I'd recommend saving a word document or something of that nature with all the code you've ever written...with helpful comments inside the code explaining what each thing does.

I know you'll think you'll remember a while from now what a line of code did...but sometimes I'll find myself asking...what the HELL did that line of code do? I know it was important...but---and I end up stumped. This way you can more easily adapt old code to do something new (very useful skill, in my opinion and experience). This has enabled me to wow my boss with quicker turnarounds (I'm not even a real programmer...I say I dabble...and then I take on whatever technical project he wants & usually teach myself as I go). For example: Suddenly he wants something to do one thing...I'd partly written some code to do something else...so I changed that a bit & voila! I had exactly what he wanted...within a few hours.