Posts by benjamint

    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<>""))

    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?


    Please see also the example on:


    https://www.box.com/s/inau9fsyxomv2nl9fxw4

    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.

    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


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


    Already found something like this:



    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.

    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

    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?

    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?

    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

    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.


    Can anyone help me please???


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



    Code
    Private Sub ComboBox2_Change()
    
    
    End Sub

    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):


    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

    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?

    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.


    Thanks in advance!!!