# Posts by benjamint

• ## Special Average Formula

Re: Special Average Formula

Already found this, looks a bit better (but thanks for your help!!):

=SUMPRODUCT(--(MOD(ROW(B3:B23),5)=3),--(MOD(ROW(B4:B24),5)=4),--(B3:B23<>""),B4:B24)/SUMPRODUCT(--(MOD(ROW(B3:B23),5)=3),--(B3:B23<>""))

• ## Special Average Formula

I have 5 products and every product has it's own correction percentage. Sometimes I fill in this percentage only for one, two, three, four or five products. The correction percentage is always filled in by default at 100%.
Is there a way to calculate the average of these percentages?
For every product I have to fill in the cell with volume, so if there is no volume filled in then also the percentage doesn't count.

I.E. I use in this case only 1 product, and set the percentage to 75%, and there are no other products (so the other 4 products are by default on 100%). I want to see on the total sheet an average of 75%. And not 475% divided by 5. Can anyone help me?

• ## Closing workbook with VBA without SAVING

Re: Closing workbook with VBA without SAVING

This is great, but when I click on YES. the original template still remains open.
I want it to close the original template file after copying to the new workbook.

• ## Closing workbook with VBA without SAVING

Re: Closing workbook with VBA without SAVING

Thanks for your help, but unfortunately I'm still getting this message box popup twice. So even when I click on NO, I get once again the same messagebox. I have to click once again on NO. The same is for the YES button.

• ## Closing workbook with VBA without SAVING

I'm trying to close a workbook without saving it. But everytime I get the message box popupped twice. Can anyone help me?

These are the codes that I use:

And the Code for: Call Copy_ActiveSheet

• ## Save sheet(s) to new document and save & close document to desktop VBA

Re: Save sheet(s) to new document and save &amp; close document to desktop

And this works, but only if there is 1 sheet active. When there is more than one sheet active I get an error at this line:

Code
``ws.Select``

And one more thing: After copying the 1 sheet (at this moment) and I click on the SAVE button it give me this message:

https://www.box.com/files/0/f/0/1/f_4105595897

I just want to save it as a default Excel file, with no macro's/vba's.

• ## Save sheet(s) to new document and save & close document to desktop VBA

Re: Save sheet(s) to new document and save &amp; close document to desktop

• ## Save sheet(s) to new document and save & close document to desktop VBA

Hi,

I made a Excel file, with Userform and many macro's. After the user has filled in the required fields, I don't want him to be able to save and overwrite the file. I just want to give him the option, upon clicking close (the red X button), to save the active sheets to a new document and save that file to the users desktop.

Anyone an ideao how I can do that with VBA?

So clicking CLOSE ==> Message: Do you want to save the file? ===> YES or NO???
If YES ==> Copy and paste the active sheets to new document and save it to the desktop.
If NO ==> Close document without saving.

And SAVE button should be disabled, so that the user cannot overwrite original file.

Also posted on: http://www.mrexcel.com/forum/e…ications.html#post3323381

• ## TextBox with only numeric values

Re: TextBox with only numeric values

Once again: Thanks a lot!!!!! This is perfect.

• ## TextBox with only numeric values

Re: TextBox with only numeric values

I'm really sorry, but one last question, then I won't bother you anymore.
Is there also a possibility to add the Tab back function, (so SHIFT + TAB), to go to the previous field/textbox?

• ## TextBox with only numeric values

Re: TextBox with only numeric values

This works great!!! I'm really thankfull.

Do you know how I can restrict the user to enter [email protected]#\$^&*() ?????
Because these signs are still possible to enter in the textbox

• ## TextBox with only numeric values

Re: TextBox with only numeric values

Wowwww, this works great. One little question: Since I entered your code, I cannot go to the next field/textbox with the Tab key. And it's only in this textbox, all the other textboxes still work with the Tab key. Any idea how to solve this?

And also: What does Case 8, 13, 46, 48 To 57, 96 To 105, 110 mean?

• ## TextBox with only numeric values

Hi,

I want to let the user only fill in an numeric value, and let the numeric value show as value + %. So if he/she enter 10, then it should show 10%. And if he/she enters 10%, it should pop up a message to only enter numeric value. And after entering the numeric value of 10, he/she should see 10%.

Right now I found something like this (see below), but the issue with is that the user still can enter non numeric values, and also i.e. 10%. After entering 10%, he/she sees 10%% (so double %). I want to prevent this. Anyone an idea how?

Code
``````Private Sub TextBox11_Exit(ByVal Cancell As MSForms.ReturnBoolean)
TextBox11.Value = TextBox11.Value & "%"
End Sub``````
• ## Filtering between combobox (with RowSource)

Hi,

I have a userform with several comboboxes, and I when I select an item from combobox1, I have made it to filter (with RowSource within VBA) and only show the possible solution in combobox2. But now I want to add combobox3 (also with RowSource), with the ability to only show combobox3 when in combobox2 "ECY" is selected. And if combobox3 is filled, then the user should be able to also fill in combobox 4 (filtering again on possible values depending on combobox3). So if the user hasn't picked in combobox2 "ECY", then he should be able to fill combobox3 and combobox4.

Please see below the code that I have for filtering between combobox1 (with RowSouce)and combobox2:

Code
``````Private Sub ComboBox2_Change()

End Sub``````
• ## Copy selected rows vba

Hi,

I want to copy some selected rows to another sheet.
I.E. Starting with cell AP1 and going down with CTRL+SHIFT+down arrow until he finds the next "X". And then select all these rows, and copy to sheet2.

What I have right now is (But it's not working):

• ## restricting textbox to fill only negative values

Re: restricting textbox to fill only negative values

I used this code and worked for me, thanks for your help guys:

Code
``````If Val(TextBox20.Value) > 0 Then
'MsgBox "Negative numbers only allowed...", vbExclamation, "Error"
'Cancel = True

'// Alternatively - Convert positive numbers
'// to negative
TextBox20.Value = Val(TextBox20.Value) * -1
Exit Sub
End If``````
• ## restricting textbox to fill only negative values

Re: restricting textbox to fill only negative values

This is almost what I'm looking for. But the only issue is when I have an empty textbox, I'm still getting a message. Is it possible to NOT getting a message on an empty textbox, but only when data is filled in?

• ## DTPicker value NULL

Hi,

I'm using DatePicker (DTPicker) in my userform, and I want the user to fill in a start-date and end-date, but when I add the DatePicker, it automatically sets the date to today. I want it to be clear, and to let the user fill it in. How can I do that?

• ## restricting textbox to fill only negative values

Hi,

I want to restrict a textbox to only let the user fill negative values. So first of all he/she shouldn't be able to fill in a text, and only values. And if a value is filled in, then it should be a negative value (automatically).

• ## Convert date from textbox

Hi,

I want to convert a date from a textbox. When I enter a date in textbox it convert to US date.
I.E. 1-11-2012 and it makes 11-1-2012 upon unloading the userform. But I want to have 01-11-2012 (so 1st November 2012)

What do I have to add/change in VBA to get 01-11-2012. And is there also a possibility to force the user to enter in MM-DD-YYYY format? And if not entered correctly, there should popup a messagebox.