Posts by Keebellah

Hi, I wrote this function to calcutae it based upon the linefeeds and that's not working (stated eaerlier by evryone)

Public Function count_TextLines(myText As Variant) As Integer
' Counts the lines by comparing the contents by the themselves after removing
' the line brakes which is character 10.
' This gives back the number of breaks so we add 1 to get the number of lines,
' since the last line doesn't have a line break.
count_TextLines = IIf(Len(Trim(myText)) = 0, 0, (Len(myText) - Len(Replace(myText, Chr(10), "")) + 2))
End Function

Maybe integrating this with merged cells columnwidths added up and the fontsize ...

I'm goinf to try that, triggered by nruvba ; nice trick will update

Just a question, the number of active command buttons may vary from 1 to 14 < I still don't want to add a whateverbutton_Click routine for each of the 14 possible buttons, I just ned one command that will return the TAG value that I need as parameter for another routine. So I'm still stuck to a class module that does that, is this a correct assumption?

Hi Roy, true and simple.

I also got some code provided by Greg M on the Excel forum that solved the issue too.

Thanks for you tip and solution

Forgot to mentio that I also posted this on the Excelforum: https://www.excelforum.com/exc…ndbutton.html#post5381743

Hi,

I have a simple Userform with 14 command buttons. Depending what routine and conditions 2 or more Command Buttons will become visible.

I found the class code to that returns the pressed button's Caption and that works, but I need to get the clicked Button's TAG.

I do not want to to add 14 Sub CommandButtonx_Click routines, I just need one common routine that will run one macro that will return the TAG value of the button clicked and the rest of the code.

Private Sub UserForm_Initialize()
Dim ctrl As MSForms.control
Dim clsObject As Class1


'Create New Collection To Store Custom
Set colButton = New Collection
For Each ctrl In Me.Controls
If TypeName(ctrl) = "CommandButton" Then
Set clsObject = New Class1
Set clsObject.Button = ctrl
colButton.Add clsObject
End If
Next
End Sub

Something similar but to Return the TAG

Thanks in advance

This is the dialogto select a printer

Function SelePrn()
    Application.Dialogs(xlDialogPrinterSetup).Show
End Function

Of course it's possible, I suggest attaching a sample file with the layout you have and want (dummy data please), it will be easier to explain.
Guessing is not such a good option to start on this

Thanks Kenneth,
Well strictly it'snot a UDF but, what's in a name
I found the reason, why my code did not work as expected, I was under the assumption I was passing the correct parameter (a file path) but it was a filepath and name and this caused the IfFolderExists() to return false and thus not the path.
The thing is I've used this GetFolder() for ages but thought it had something to do with the UNC paths.
Well, it was more of a user's mistake and it takes a pair of extra eyes to make me look at it better.
Thanks for looking into it, and yes, it's solved.

Can't seem to find the MARK SOLVED option or button

Hi, I just got the message today, will see what I can do for you.
Cheers,

Hi guys,
The code I copied a long time ago from the OzGrid site but I still haven't been able to figure out how to set the folder to start in using the UDF

Function GetFolder(strPath As String, Optional sTitle As String) As String
Dim fldr As FileDialog
Dim sItem As String
isOk = FolderExists(strPath)
If Len(Trim(strPath)) = 0 Or isOk = False Then
    strPath = ""
End If


Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .InitialFileName = strPath
    .Title = "Select " & IIf(Len(Trim(sTitle)) > 0, sTitle, "a Folder:")
    .AllowMultiSelect = False
    .ButtonName = "Select folder"
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With
NextCode:
GetFolder = sItem
Set fldr = Nothing
End Function

I pass the parameter (which is valid) as strPath (with and without the final backslash) and it always starts in a previously selected folder.
Even if the value for InitialFileName is filled or not

the value in strPath can either be a UNC share or a local folder.

Can somebody point me in the right direction?

I cannot use ChDir because it will not work, especially if it points to a network share (UNC path)

Hope somebody has an idea.
Thanks

If you would take the time to attach the file (remove sensitive data) it will make is easier to work with the data instead of filling in something ourselfves

Re: Personal.xlsb Project is Unviewable - Excel 2010

This one helped me today (9 yeras and 3 months later) Thanks

Re: copy and paste using ctl short key?

And this:

Sub SelectAll()
    Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
End Sub

Will setelc all to the last filles row/column starting from cell A2

Re: copy and paste using ctl short key?

Do you want to do using VBA (macro) or just the range?
Once you select the range press Ctrl+C, go to the other sheet or file and select the cell where the first field swill come and press Ctlr+V.
With VBA you coul also record the macro while doing this and see how the code looks like after you stop recordding the macro and edit it to your further needs.

Re: copy and paste using ctl short key?

Shift+Ctrl+End to slect the last filled row-column