Posts by muabao

    Hello,


    I have a text file with 10 rows of data, all single column so no delimiter needs to be set. In the Excel sheet, I would like to selectively import the rows like this: A1 through A5 in the Excel file gets the data from rows 1-5 from the text file; C1-C3 gets the data from rows 6-9 from the text file; D4 gets row 10.


    How would I code in VBA to perform the above tasks?


    This is what I currently have:


    The code was from a recorded macro. However, all 10 rows are copied into the Excel cells in the same column when I run the code.


    Any help is greatly appreciated.


    MB

    Re: Userform Causing Lockup?


    Here's what I have in the initialize event:
    [VBA]
    Private Sub UserForm_Initialize()
    TextBox1.Value = NullString
    TextBox2.Value = NullString
    End Sub
    [/VBA]

    Hi All,


    I'm testing some userform code & it seems to cause some sort of lockup where I can't enter anything into the excel cells. It doesn't just affect the spreadsheet the userform is attached to but any spreadsheet that I have open.


    Has anyone experienced the same problem &, if so, how do I fix it?


    TIA for any help.
    MB

    Hi All,


    I would like to design one single Userform that I can call on to retrieve data input from the user. The userform has 3 textbox fields. I need to load the user form 4 times. Each time, the values from the 3 textboxes will be copied to a specfic set of cells. How do I pass these values to the cells without having to have 4 separate sets of the same userform? In other words, do I pass the range of the cells to the userform object in order for the correct entries to be copied to the correct cells?


    Thx for any help.

    Hi All,


    I have a VBA user form that asks for user's input. The form has 3 textbox. I would like to check for the entry & make sure that it's numeric & not null. If it's not numeric or null, I'd like to display a warning message & highlight the textbox & ask for entry again.


    Here's what I have but it's not really working. The warning message will come up but the next textbox is highlighted:


    [VBA]
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Not IsNumeric(Me.TextBox1.Value) Then
    MsgBox "Please Enter Only Numeric Values"
    TextBox1.SetFocus
    End If

    End Sub


    Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Not IsNumeric(Me.TextBox2.Value) Then
    MsgBox "Please Enter Only Numeric Values"
    TextBox2.SetFocus
    End If

    End Sub


    Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    If Not IsNumeric(Me.TextBox3.Value) Then
    MsgBox "Please Enter Only Numeric Values"
    TextBox3.SetFocus
    End If

    End Sub
    [/VBA]


    Any help is greatly appreciated.

    Hello,


    Has anyone here used VBA to talk to an equipment via a serial port?


    I'm trying to run the following (attenuate the power of an optical attenuator) but got an error:


    Code
    Sub AttenuatePower()
        
        Open "COM1:1200,N,8,1" For Random As #3
        Print #3, "WVL 1550e-9 m"
        atten = 20
        Print #3, "ATT "; atten; " dB"
        End
    
    
    End Sub



    The error is Run-time error 54, bad file mode.


    Line number 2 (Print #3, "WVL 1550e-9 m") is highlighted as the one causing the error.


    Any help is greatly appreciated.


    MB

    Hello,


    I did a search & couldn't find anything related to my question:


    If I have a range of known cells (A1, A3, A5, for ex.) & want to find which cell has the largest value & then return the cell of that contains the largest value (not the value itself), how do I code it it VBA?


    TIA for any help.
    MB

    Re: Extracting Known Text From A Text File


    Thank you for your help!


    I got an error running your code. The error said "Compile Error: User-defined type not defined" & the "Dim fso As New FileSystemObject" declaration line is highlighted.


    Thx again for your help.
    MB

    Hello,


    How do I open a text file, read all the lines from said text file & extract a value in the form of a string. For ex., if I know that a certain line in the file might contain this text: Market Value=1234, then I would like to extract the value 1234 (the text Market Value is a constant) from that line & dumps it into a cell in the Excel file.


    TIA for any help.
    MB

    Re: Scope Of Public Variable


    What I'm trying to do is that when the excel file is open, it'll automatically parse the name of the file & determine the product's name (which is the string -however long- before the hypen in the file's name). Then the product's name is made available to all the modules which contain subroutines that will refer to the product's name.


    What's the best way to do this?


    Thanks again,
    MB

    Hi,


    I have a few lines of code as follows:


    It would return "Test" if the file's name is "Test-Part1.xls"


    I then declare the productName variable as Public (Public productName As String) in one of the modules. But the productName only holds the value for a certain amount of time. After a while, it's empty.


    Could you tell me if I did something wrong & how to remedy it?


    TIA for any help.
    MB

    Hi,


    Once I'm able to extract the name of an active workbook (say "Test"), how do I go about assigning it to a Global Variable that can be accessed by all the subs that reside within a certain module?


    Thanks for any help,
    MB

    Hi,


    I must've not searched with the correct keywords but I couldn't find help on how to extract the current/active worksheet's file's name using VBA code.


    Let's say I have a an excel file with the name "Test.xls" Which function do I use to return Test (with or without the extension)?


    TIA for any help.


    MB

    Re: Programming Buttons


    I have more than 1 cell that I'd like to insert the text in so I thought maybe I can link the cell to its relative location will help with the VBA code.


    Thx,
    MB

    Hello,


    Is there a way to program a VBA button so that its cell location is known. For ex., I have a button that says "Fail". I'd like to program it in such a way that when the user clicks on it, the text "Fail" is inserted into the cell next to it. How do I avoid hard-coding the cell reference?


    TIA for any help


    MB

    Hi All,


    How do I code VBA to extract the data (text) from a cell at a certain position? For ex., if the cell contains this text: "My Name Is MB" I want to extract the text starting from position 4 so that the result is "Name Is MB"


    Any help is greatly appreciated.


    Thx,
    MuaBao

    Hi Everyone,
    This code below is pasted from Automatically Add Date, Time or Date & Time and uses the [wce]*[/wce]


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
            If Not Intersect(Target, Range("A2:A100")) Is Nothing Then
                With Target(1, 2)
                    .Value = Date
                    .EntireColumn.AutoFit
                End With
            End If
    End Sub


    It automatically enters today's date into the corresponding B cell. How do I code it so that the date is automatically removed when & if I delete the corresponding A cell?


    Any help is greatly appreciated.


    Thx,
    MB