Posts by nikolassor

Re: UserForm will Neither Hide nor Unload

Do you just want your userform to hide until your next move?
If so, did you try userfomname.visible =false ? And after you do your thing you can set it back to true?

Re: Problem with ontime.application

Aftr the macro1 has finished, you want to do it again right? So just add

call Schedulemacro

before End Sub inside Macro1

Re: VBA loop through array but skipping blank cells

You can try something like this:

For x = 1 to y
    If Not x / 4 = Int(x / 4) then
        'get data
    End If
Next

Re: listview userform was not working

You used a listbox than a listview. It is copmletely different...
In your sample, I delete the listbox and put a listview with the same name 'lvwTest' and without change anything else, it works.

You must use a listview! In your toolbox, go to 'Additional Controls' and check the ' Microsoft ListView Control, version... '. Then you can see it in toolbox.

Re: listview userform was not working

Can you sent a sample of your workbook?

Re: listview userform was not working

If you create the userform in this pc, i don't know why it gives you the message. Maybe you changed something?

Go t vba editor and check your references under tools. There must be a missing thing.

Try to rebuild the userforn yourself. Not copy/paste.

Re: Add rows to tables

A way to do this is to define the new-sheet's name by counting them and pick the last's one. Of course, this means that your new sheet must always be the last of your sheets (or at the same place).

When I need to do exactly the same thing in different sheets, I prefer to have the macro on my ribbon tab. So I just use the activesheet as my sheet.

Re: Complex IF (AND (OR formula

I think you first have to put the AND inside OR.
Try this:
IF(OR(AND(D2>2,D3<3),AND(D2<3,D3>2)),D2+D3&"("&D2-D3&")",(D2+D3))

Re: Copy and Rename a Worksheet

Add this line After Dim line

Worksheets("Do Not Use").Visible = True

And this Before End Sub

Worksheets("Do Not Use").Visible = False

Re: Dynamic Userform Design

Maybe this can help you. If I understand correct, you have to put all your textboxes inside a frame and use a class on frame_ExitEvent.

(With a listbox you don't have to check the values inside your list. Just the textboxes that you use to enter your data...)

Re: Copy and Rename a Worksheet

You can try this:

Sub AddWs()
    Dim i As Long, wsName As String, temp As String
    Sheets("Do Not Use").Copy After:=Sheets(Sheets.Count)
    wsName = "New Tennant"
     
    If ShtExists(wsName) Then
        temp = Left(wsName, 3)
        i = 1
        wsName = temp & i
        Do While ShtExists(wsName)
            i = i + 1
            wsName = temp & i
        Loop
    End If
    ActiveSheet.Name = wsName
End Sub




Function ShtExists(wsName As String)
   Dim s As Excel.Worksheet
   On Error Resume Next
   Set s = ActiveWorkbook.Sheets(wsName)
   On Error GoTo 0
   ShtExists = Not s Is Nothing
End Function

Re: Dynamic Userform Design

I don't know if it suits you, but did you try to use a listbox instead of textboxes?
You can have a a simple line of your textboxes and then 2 or 3 buttons to add / edit /delete on your listbox.

Re: listview userform was not working

You mean something like this??

Re: converting date format

In the attached workbook, in sheet1, I set the B1 and B2 cells format (under 'Home' tab and 'Number' section) as "short date". Also, I did the same for E:E column.
Then I just copied your formula in F1 cell and gives me the result. My dates are also in "dd/mm/yyyy" format...

Hope that helps...

Re: converting date format

If I have both my "b1" and "e:e" ranges formated as 'short date', your countifs works for me. Counts all dates that are equal to "b1" date...
Why you need a date() formula inside countifs() ? Am I missing something??

Re: Vba changes to be made

Yes but he could set his zip name that way. If it is always changing. Just for a method not to have retyping his code all the time.
Right?

Re: Vba changes to be made

Something like this maybe?

strFileName = Application.GetOpenFilename("zip Files(*.zip),*.zip")
    'strFileName = "fo26DEC2016bhav.csv"
    
    str7ZIP = "C:\Program Files (x86)\7-Zip\7z.exe"
    strDestinationFolder = ActiveWorkbook.Path
    
    strZipFile = strDestinationFolder & "\" & strFileName
    'strZipFile = strDestinationFolder & "\fo26DEC2016bhav.csv.zip"

Just changed the 'strFileName' and 'strZipFile' lines