XL03: Macro VB code for User input box and Invoice Quantity calculation

TechStudent313

New Member
Joined
May 9, 2010
Messages
1
Hello i have just joined up after browsing through the many helpful theads on the Excel forum for different parts of spreadsheet work and i have such 2 queries regarding an Invoice spreadsheet system i am working on in MS Excel 2003. The system itself includes an Invoice sheet, customer and Products datasheets which i am linking the information using a Vlookup formula that includes error trapping on the Invoice sheet.

Macro code for User input box problem

Currently i have written out a piece of VB code for a Macro on the customer datasheet that i am aiming to tether to a command button to prompt the user via Input boxes to enter in the information for 7/8 collumns on the table (will do the same for the products table once the problem has been passed).

My problem is that after using the following VB code, the input boxes appear as required and allow me to type information in but it also prompts me to enter information for the first Collumn which has a unique 3 numbered ID in which is the collumn that i dont want to be entering data into, want it to skip and only query the user from collumn B onwards:

ActiveCell = Application.InputBox("Enter the customer's name: ", _
"Name", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

ActiveCell = Application.InputBox("Enter the customer's address: ", _
"Address", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

ActiveCell = Application.InputBox("Enter the customer's town: ", _
"Town", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

ActiveCell = Application.InputBox("Enter the customer's county: ", _
"County", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

ActiveCell = Application.InputBox("Enter the customer's postcode: ", _
"Postcode", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

ActiveCell = Application.InputBox("Enter the customer's phone: ", _
"Phone_No.", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select

ActiveCell = Application.InputBox("Enter the customer's fax: ", _
"Fax_No.", , , , , 2)
ActiveCell.Offset(0,1).Range("A1").select


At first i thought i would only need to adjust the Offset or range numbers and it does adjust where the information the user types into the input boxes appears but only after asking for information to add to collumn A which is what i am trying to avoid if possible (Aiming to have some sort of Autonumber function in this collumn which automatically counts up when a row is added or down when deleted).

Invoice Quantity calculation problem

My other problem regards the Invoice page itself which has 8 different Collumns (Pip COde, Description, Quantity, Price per single, VAT Rate, Net Value, VAT amount and Vat Code) all collumns use the Error trapping + vlookup functions that i mentioned earlier apart from the quantity and Net Value. I am trying to somehow multiply the Net value which at the current moment has the function =IF(I18=1,E18*0.05,IF(I18=2,E18*0.175,E18)) to work out the price ex VAT by whatever number the user adds to the Quantity collumn. (Quantity collumn currently has no functions added to it). Normally i would go with the G17*D17 but when entering it since it is already working out one calculation i get the Circular reference error. An idea i did think of was just add another collumn and use the simple code with that however, but i would prefer if i could have it within the same collumn as Net Value to save space.

Thanks for your time.

Any ideas?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,452
Macro code for User input box problem
Code:
    Dim arrPrompts As Variant, i As Integer, sReply As String
    
    arrPrompts = Array("Name", "Address", "Town", "Country", "Postcode", "Phone", "Fax")
    
    For i = 0 To UBound(arrPrompts)
        sReply = Application.InputBox("Enter the customer's " & LCase(arrPrompts(i)) & ":", arrPrompts(i), Type:=2)
        If sReply <> "False" Then Range("B1").Offset(0, i) = sReply
    Next i


Invoice Quantity calculation problem
=IF(I18=1,E18*0.05,IF(I18=2,E18*0.175,E18))*D18
 

Forum statistics

Threads
1,175,515
Messages
5,897,885
Members
434,686
Latest member
asdf12l3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top