Re: Cell Comment
I do not know how to link the post, but anyway I have tried copy and paste the link
Re: Cell Comment
I do not know how to link the post, but anyway I have tried copy and paste the link
This thread was posted by Pratibha
It is exactly what I was looking for a long time but need few changes
The example is of Pratibha, with VBA code
After you click the command button in "data" sheet the information is generated in "data1" sheet.
The VBA code is present in module,
I want the Comment Box on each Cell (Just like Pratibha post).
I have even given you the example in sheet1 as to what I require.
The same should be displayed in 'data1' sheet after the VBA code.
When items are addeded or deleted the comment text should be displayed.
Blank cells can be ignored.
Sorry for the repeated post but there is a minor change in comparasion of post by Pratibha.
Only difference between Pratibha and this post is that the comment in each cells should appear without changing the VBA code in my attached sheet.
Thanks
Re: combobox list help
Thanks perfect
I have made the following formula
Sheet9.Range("B1:B" & Sheet9.Cells(Rows.Count, "B").End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlNo
Sheet9.Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).CurrentRegion.Sort , key1:=Sheet9.Range("B1"), order1:=xlAscending, Header:=xlGuess
.ComboBox6.List = Sheet9.Range("B1:B" & Sheet9.Cells(Rows.Count, "B").End(xlUp).Row).Value
Here when there are 2 or more than 2 names in column B the code works fine,
But when there is only a single entry in column B it gives me a debug error 381 in the following line.
Re: allow certain text in cell
extremely sorry typing mistake the range is C7 to C500 and the rows increase by atleast 15-20 each month.
Is there any way by which only certain text can be entered in cell for eg.
Range C7 to C50, only words like A-4434 , B-3456 , C-6789 can be entered.
Secondly when the user selects any cell from C7 to C50 a comment box should appear stating "Enter A-4434 or B-3456 or C-6789", and any other text entered besides the one mentioned would give an error.
Secondly if the user types 4434 then automatically A-4434 should be written.
for 3456, text B-3456 should be written
same for others
I know this is possible with Data validation, but since validating all the cells using data validation would make the file size bigger I was looking for a option using VBA code.
I am trying to create a data generation VBA, as explained below
Please see the attached file.
Sheet1 contains the data to be copied.
Sheet2 contains the sample that I require.
I have also created a user form. Now the combinations I require are as follows.
Firstly all the data from Column A in sheet1 needs to be arranged vertically in sheet2 removing the duplicates
Then all the date from column D in sheet1 needs to be copied to sheet2 removing the duplicates.
Now
1. When Type is selected in Combobox1 and All months is selected in combobox2 then how many times the particular item is appearing in given alphabet needs to be mentioned.
2. When Qty is selected in Combobox1 and All months is selected in combobox2 then qty should be written matching the given alphabet.
3. When Type is selected in Combobox1 and January is selected in combobox2 then how many times the particular item is appearing in January against given alphabet needs to be mentioned.
4. The total in vertical and horizontal together with serial number and particualrs should be mentioned.
Example of what I require is shown in sheet2
As the data keeps increasing whenever I put more data the total column vertical and horizontal should also shift accordingy
With reference to my earlier post, I would cut short on what I require.
I am trying to create a data generation VBA, hope someone could help me as I find it highly complicated, but I need the same for my work assessment.
Please see the attached file.
Sheet1 contains the data to be copied.
Sheet2 contains the sample that I require.
I have also created a user form. Now the combinations I require are as follows.
Firstly all the data from Column A in sheet1 needs to be arranged vertically in sheet2 removing the duplicates
Then all the date from column D in sheet1 needs to be copied to sheet2 removing the duplicates.
Now
1. When Type is selected in Combobox1 and All months is selected in combobox2 then how many times the particular item is appearing in given alphabet needs to be mentioned.
2. When Qty is selected in Combobox1 and All months is selected in combobox2 then qty should be written matching the given alphabet.
3. The total in vertical and horizontal together with serial number and particualrs should be mentioned.
Example of what I require is shown in sheet2
As the data keeps increasing whenever I put more data the total column vertical and horizontal should also shift accordingy
I have asked the below query before, but since there was no respose I am asking it again, as I need for my work assesment.
I am trying to create a data generation VBA, hope someone could help me as I find it highly complicated, but I need the same for my work assessment.
Please see the attached file.
Sheet1 contains the data to be copied.
Sheet2 contains the sample that I require.
I have also created a user form. Now the combinations I require are as follows.
Firstly all the data from Column A in sheet1 needs to be arranged vertically in sheet2 removing the duplicates
Then all the date from column D in sheet1 needs to be copied to sheet2 removing the duplicates.
Now
1. When Type is selected in Combobox1 and All months is selected in combobox2 then how many times the particular item is appearing in given alphabet needs to be mentioned.
2. When Qty is selected in Combobox1 and All months is selected in combobox2 then qty should be written matching the given alphabet.
3. When Amt is selected in Combobox1 and All months is selected in combobox2 then Amt should be written matching the given alphabet.
4. Same way when Amt Or Qty Or Type is selected from combobox1 and month is selected from combobox3 then how many times the item appearing in that particular month needs to be mentioned.
The total in vertical and horizontal together with serial number and particualrs should be mentioned.
As the data keeps increasing whenever I put more data the total column vertical and horizontal should also shift accordingy
Lastly I need the caption on sheet2 row3 when following is selected with merge and bold
Combobox1 "Types" and Combobox2 All Months then caption as : "TOTAL TYPES FOR ALL MONTHS"
Combobox1 "QTY" and Combobox2 All Months then caption as : "TOTAL QTY FOR ALL MONTHS"
Combobox1 "Amt" and Combobox2 All Months then caption as : "TOTAL AMT FOR ALL MONTHS"
Combobox1 "Types" and Combobox2 (SELECTED MONTH) then caption as : "TOTAL TYPES FOR (SELECTED MONTH)"
Combobox1 "QTY" and Combobox2 (SELECTED MONTH) then caption as : "TOTAL QTY FOR (SELECTED MONTH)"
Combobox1 "Amt" and Combobox2 (SELECTED MONTH) then caption as : "TOTAL AMT FOR (SELECTED MONTH)"
Formating and Borders as shown with Font as Arial and size 10.
Re: Copy and Sort using VBA
I am sorry for replying after a long time.
Actually your code works perfectly well only thing what I require is as follows.
1. Apologies from my side as I was not clear in my post. As in my post, I had mentioned page break after every 44 lines, what I actually meant was that, assuming the page has 50 lines then the data should be copied till 44 lines then balance on next page.
2. Serial number means that id data is present in any column from D7, then serial number should come in column C7
I would like to copy Sheet1, col C starting from C7 to the last cell of data entered to Sheet2 from D7.
The list of data would change as and when I enter more data.
After Copy I would like the list to be sorted in ascending order.
Page Break after every 44 lines
Next page should begin after 6 lines and same as above , page break after 44 lines
Secondly I would like to mention serial numbers, for eg. in sheet 2 from C7 if data if data is present in D7 then 1 should be mentioned and so on.
I have made a userform with 2 textboxes in which I would add data
I want to make a VBA code so that the data in textbox1 searches entire coloumn in C right up to the last data and if the value in texbox1 matches or is found in col C then a message box appears. If no match is found then it goes to proceed for another funtion.
The next textbox should search for in col. D
I do not know if Vlookup is proper or any other formula, please help