Hi
Hope you can help me. I’m trying to create an excel data-entry form using VBA to capture data and -- when submitted -- paste the data onto a separate tab on the spreadsheet. This is what I have so far.
This form will be used by my colleagues when they collect information from customers. At the moment they are collecting the info and putting it on a word document to email over and most of the time the wrong information is provided, and it takes a long time to rectify. I want to get this Form to work so that the information can be provided correctly through Sharepoint in Microsoft Teams. It’s important that multiple people can use the Form at the same time.
I need help applying the following restrictions. Any help would be appreciated!
When the user enters data into the AccountReferenceTextBox, we need it to do the following:
The user must select an option from the TenantOneTitleComboBox before they can submit the Form.
When the user enters data into the TenantOneForenameTextBox – we need to do the following:
When the user enters data into the TenantOneSurnameTextBox – we need to do the following:
The above should also apply to the following:
When the user enters data into the PostCodeTextBox – we need it to do the following:
When the user enters data into the FLATextBox – we need it to do the following:
The user must select an option from the HouseholdCompositionComboBox before they can submit the Form.
When the user enters data into the WaterRatesTextBox – we need it to do the following:
WaterRatesTextBox must be filled out before the user can submit the form.
When the user enters data into the NetRentTextBox – we need it to do the following:
NetRentTextBox must be filled out before the user can submit the Form.
When the user enters data into the HousingBenefitTextBox, we need it to do the following:
The user must select an option from the UCVerComboBox before they can submit the Form
When the user enters data into the UCVerTextBox, we need it to do the following:
The user must select an option from the PermissionComboBox before they can submit the Form
The user must select an option from the AuditComboBox before they can submit the Form
The user must select an option from the HouseholdIncomeOneComboBox before they can submit the Form
When the user enters data into the HouseholdIncomeOneAmountTextBox, we need it to do the following:
If the mandatory fields are not answered, we need an error message to be returned when they attempt to submit the form stating which mandatory fields have not been filled out
Hope you can help me. I’m trying to create an excel data-entry form using VBA to capture data and -- when submitted -- paste the data onto a separate tab on the spreadsheet. This is what I have so far.
This form will be used by my colleagues when they collect information from customers. At the moment they are collecting the info and putting it on a word document to email over and most of the time the wrong information is provided, and it takes a long time to rectify. I want to get this Form to work so that the information can be provided correctly through Sharepoint in Microsoft Teams. It’s important that multiple people can use the Form at the same time.
I need help applying the following restrictions. Any help would be appreciated!
When the user enters data into the AccountReferenceTextBox, we need it to do the following:
- be restricted to 14 characters
- only allow numbers
- not allow any spaces
- Return an error message stating the following if anything other than a number is entered, or a number that is less than 14-digits-long is entered: “Please check that you’ve entered the correct number of digits and/or omitted any hyphens and/or spaces.”
The user must select an option from the TenantOneTitleComboBox before they can submit the Form.
When the user enters data into the TenantOneForenameTextBox – we need to do the following:
- not allow any spaces
- capitalise the first character entered into TenantOneForenameTextBox or insist on the user capitalising the first character of the forename.
- only allow characters, one hyphen in case the customer has a double-barrel Forename, and one apostrophe in case the customer has a surname such as De’wayne.
- The first character entered after a hyphen needs to be capitalised.
- Return an error message stating the following if any spaces, extra hyphens, or extra apostrophes are entered: “This field does not accept spaces and/or more than one hyphen and/or apostrophe."
When the user enters data into the TenantOneSurnameTextBox – we need to do the following:
- not allow any spaces
- capitalise the first character entered into TenantOneSurnameTextBox or insist on the user capitalising the first character of the surname.
- only allow characters, one hyphen in case the customer has a double-barrel surname, and one apostrophe in case the customer has a surname such as O'Brien. The first character entered after a hyphen or apostrophe needs to be capitalised.
- Return an error message stating the following if any spaces, more than one hyphens, or more than one apostrophe is entered: “This field does not accept spaces and/or more than one hyphen and/or apostrophe."
The above should also apply to the following:
- TenantTwoTitleComboBox
- TenantThreeTitleComboBox
- TenantTwoForenameTextBox
- TenantThreeForenameTextBox
- TenantTwoSurnameTextBox
- TenantThreeSurnameTextBox
- Except these don’t have to be filled out unlike TenantOneTitleComboBox, TenantOneForenameTextBox and TenantOneSurnameTextBox which must. We also need to give the user the option to delete their selection in case they clicked it by mistake as I have all the combo boxes set to drop down list so that anything other than what’s in the drop down menu cannot be typed or selected.
- be restricted to 10 characters
- not allow the user to enter anything other than numbers and forward slashes. This is so the data is captured in the following format: 01/01/2010. If anyone knows a way of inserting a calendar so that the user can select a date from that, that’d be really useful!
- Return an error message stating the following if any spaces and/or anything other than a number or forward slash is entered: “This field must be entered in the following format: 01/01/2023.”
When the user enters data into the PostCodeTextBox – we need it to do the following:
- be restricted to 8 characters.
- only allow one space
- only allow capital letters and numbers.
- Return an error message stating the following if the above conditions are not met: "This field must be entered in the following format: S8 7LA."
When the user enters data into the FLATextBox – we need it to do the following:
- Not to allow anything other than numbers, characters, and a maximum of two commas that must have a space after them.
- There must be a number or Capitalised character after each space
- Return an error message stating the following if the above conditions are not met: “This field does not accept anything other than numbers, characters, and a maximum of two commas."
The user must select an option from the HouseholdCompositionComboBox before they can submit the Form.
When the user enters data into the WaterRatesTextBox – we need it to do the following:
- be restricted to 6 characters – these six characters should also include a pound symbol which has already been inserted into the WaterRatesTextBox when the user opens the Form and can’t be removed. This is so that the data is input in the following format i.e. to two decimal places: £10.00
WaterRatesTextBox must be filled out before the user can submit the form.
When the user enters data into the NetRentTextBox – we need it to do the following:
- Only allow numbers
- be restricted to 7 characters - these seven characters should also include a pound symbol which has already been inserted into the NetRentTextBox when the user opens the Form and can’t be removed. This is so that the data is input in the following format i.e. to two decimal places: £100.00.
NetRentTextBox must be filled out before the user can submit the Form.
When the user enters data into the HousingBenefitTextBox, we need it to do the following:
- Only allow numbers
- be restricted to 7 characters - these seven characters should also include a pound symbol which has already been inserted into the HousingBenefitTextBox when the user opens the Form and can’t be removed. This is so the data is input in the following format i.e. to two decimal places: £100.00.
- If the user selects No from HousingBenefitComboBox – then it enters the following into HousingBenefitTextBox: “£0.00”. If the user selects or changes the answer to yes, then it allows them to type any amount other than £0.00 into HousingBenefitTextBox.
The user must select an option from the UCVerComboBox before they can submit the Form
When the user enters data into the UCVerTextBox, we need it to do the following:
- Only allow numbers
- be restricted to 7 characters - these seven characters should also include a pound symbol which has already been inserted into the UCVerComboBox when the user opens the Form and can’t be removed. This is so the data is input in the following format i.e. to two decimal places: £100.00.
- If the user selects No from UCVerComboBox – then it enters the following into UCVerTextBox: “£0.00”. If the user selects or changes the answer to yes, then it allows them to type any amount other than £0.00 into UCVerTextBox.
The user must select an option from the PermissionComboBox before they can submit the Form
The user must select an option from the AuditComboBox before they can submit the Form
The user must select an option from the HouseholdIncomeOneComboBox before they can submit the Form
When the user enters data into the HouseholdIncomeOneAmountTextBox, we need it to do the following:
- Only allow numbers
- be restricted to 8 characters - these eight characters should also include a pound symbol which has already been inserted into the HouseholdIncomeOneAmountTextBox when the user opens the Form and can’t be removed. This is so that the data is input in the following format i.e. to two decimal places: £1000.00
- HouseholdIncomeTwoAmountTextBox
- HouseholdIncomeThreeAmountTextBox
- HouseholdIncomeFourAmountTextBox
- HouseholdIncomeFiveAmountTextBox
- HouseholdIncomeSixAmountTextBox
- HouseholdIncomeSevenAmountTextBox
- HouseholdIncomeFrequencyTwoComboBox
- HouseholdIncomeFrequencyThreeComboBox
- HouseholdIncomeFrequencyFourComboBox
- HouseholdIncomeFrequencyFiveComboBox
- HouseholdIncomeFrequencySixComboBox
- HouseholdIncomeFrequencySevenComboBox
- Except these don’t have to be filled out unlike HouseholdIncomeOneAmountTextBox and HouseholdIncomeFrequencyOneComboBox which must. We need to give the user the option to delete their selection in case they clicked it by mistake as I have all the combo boxes set to drop down list so that anything other than what’s in the drop down menu cannot be typed or selected.
If the mandatory fields are not answered, we need an error message to be returned when they attempt to submit the form stating which mandatory fields have not been filled out
