mollovg
08-23-2011, 03:35 AM
Hello Everyone,
I ma a novice to excel and VB. I want to make a program in an excel workbook that adds new client details to the workbook with an Input Box. So far I have achievded to add new client info bu it doesnt go to the new empty row below the last one I entered and I dont know why. All I wanted to do for the moment was to have client info be entered into the workbook but since I have column description at the top as well the information would have to be entered after the 11 row. I have been going at this for days now and cant figure out how to do it tried with For Next loop to add the info and a new column but doesnt work. Any help would be greatly appreciated.
I will paste the code I have so far here:
Sub Button1_Click()
Dim emptyRow As Long
Dim i As Integer
Dim n As Integer
Dim m As Long
Dim a As Integer
'Make Sheet2 Active
Sheets(2).Activate
Dim lastrow
Dim c As Integer
Dim ClientStat As String
Dim StrCustRef As String
Dim StrCompName As String
Dim ContactName As String
Dim Telephone As Single
Dim email As String
Dim ContDate As Date
Dim DateSentOffice As Date
Dim ClientReplyDate As Date
Dim ListSentDate As Date
Dim orders As String
Dim linkOrders As String
ClientStat = "ES"
lastrow = Selection.Rows.Count
m = ActiveCell.Row
For i = m To lastrow
StrCustRef = InputBox("Please enter Customer Reference")
StrCompName = InputBox("Please Enter Company Name")
ContactName = InputBox("Please enter the name of the person you were in contact with")
Telephone = InputBox("Please enter client telephone Number")
email = InputBox("Please enter client email Adress")
ListSentDate = InputBox("Please Enter the Date the List and/or presentation were sent to the client")
ContDate = InputBox("Please enter the date when the client replyied to the presentation")
DateSentOffice = InputBox("Please enter the date the client request was sent to the office")
ClientReplyDate = InputBox("Please enter the date the client replied")
orders = InputBox("Orders Y/N")
Sheets("Sheet2").Range("A" & i) = ClientStat
Sheets("Sheet2").Range("B" & i) = StrCustRef
Sheets("Sheet2").Range("C" & i) = StrCompName
Sheets("Sheet2").Range("D" & i) = ContactName
Sheets("Sheet2").Range("E" & i) = Telephone
Sheets("Sheet2").Range("F" & i) = email
Sheets("Sheet2").Range("G" & i) = ContDate
Sheets("Sheet2").Range("H" & i) = DateSentOffice
Sheets("Sheet2").Range("I" & i) = ClientReplyDate
Sheets("Sheet2").Range("J" & i) = ListSentDate
Sheets("Sheet2").Range("K" & i) = orders
Next i
End Sub
Thnka you.
I ma a novice to excel and VB. I want to make a program in an excel workbook that adds new client details to the workbook with an Input Box. So far I have achievded to add new client info bu it doesnt go to the new empty row below the last one I entered and I dont know why. All I wanted to do for the moment was to have client info be entered into the workbook but since I have column description at the top as well the information would have to be entered after the 11 row. I have been going at this for days now and cant figure out how to do it tried with For Next loop to add the info and a new column but doesnt work. Any help would be greatly appreciated.
I will paste the code I have so far here:
Sub Button1_Click()
Dim emptyRow As Long
Dim i As Integer
Dim n As Integer
Dim m As Long
Dim a As Integer
'Make Sheet2 Active
Sheets(2).Activate
Dim lastrow
Dim c As Integer
Dim ClientStat As String
Dim StrCustRef As String
Dim StrCompName As String
Dim ContactName As String
Dim Telephone As Single
Dim email As String
Dim ContDate As Date
Dim DateSentOffice As Date
Dim ClientReplyDate As Date
Dim ListSentDate As Date
Dim orders As String
Dim linkOrders As String
ClientStat = "ES"
lastrow = Selection.Rows.Count
m = ActiveCell.Row
For i = m To lastrow
StrCustRef = InputBox("Please enter Customer Reference")
StrCompName = InputBox("Please Enter Company Name")
ContactName = InputBox("Please enter the name of the person you were in contact with")
Telephone = InputBox("Please enter client telephone Number")
email = InputBox("Please enter client email Adress")
ListSentDate = InputBox("Please Enter the Date the List and/or presentation were sent to the client")
ContDate = InputBox("Please enter the date when the client replyied to the presentation")
DateSentOffice = InputBox("Please enter the date the client request was sent to the office")
ClientReplyDate = InputBox("Please enter the date the client replied")
orders = InputBox("Orders Y/N")
Sheets("Sheet2").Range("A" & i) = ClientStat
Sheets("Sheet2").Range("B" & i) = StrCustRef
Sheets("Sheet2").Range("C" & i) = StrCompName
Sheets("Sheet2").Range("D" & i) = ContactName
Sheets("Sheet2").Range("E" & i) = Telephone
Sheets("Sheet2").Range("F" & i) = email
Sheets("Sheet2").Range("G" & i) = ContDate
Sheets("Sheet2").Range("H" & i) = DateSentOffice
Sheets("Sheet2").Range("I" & i) = ClientReplyDate
Sheets("Sheet2").Range("J" & i) = ListSentDate
Sheets("Sheet2").Range("K" & i) = orders
Next i
End Sub
Thnka you.