Posts by Rich_z

Re: Prevent Erroneous Data Being Entered

Hi,

You could try the following:

Private Sub CommandButton1_Click()
        Dim Test_Form As Worksheet
        Dim Start_Row As Integer
        Dim Call_No As Integer
        Dim Target_Row As Integer
        '*
        On Error GoTo No_Find
        Set TestForm = Worksheets("TestForm")
        Start_Row = TestForm.Range("A1:A65535").Find(What:=Me.ComboBox1.Value, LookIn:=xlValues, LookAt:=xlWhole).Row
        Call_No = Val(Mid(Me.ComboBox2.Value, 5))
        If Call_No > 0 Then
           Target_Row = Start_Row + Call_No
           With Sheet1
                'Range("B" & Target_Row).Value = Me.txt_Results_Open.Value
                'Range("C" & Target_Row).Value = Me.txt_Results_Neg.Value
                'Range("D" & Target_Row).Value = Me.txt_Results_Com.Value
                'Range("E" & Target_Row).Value = Me.txt_Results_Addval
                Range("G" & Target_Row).Value = Me.TextBox5.Value
                'Range("H" & Target_Row).Value = Me.txt_Inum.Value
                'Range("I" & Target_Row).Value = Me.txt_Comments.Value
                'Range("P" & Target_Row).Value = Me.chk_Remote.Value
                'Range("R" & Target_Row).Value = Me.chk_Side.Value
           End With
        End If
cb1_Exit:
        Set Test_Form = Nothing
        On Error GoTo 0
        Exit Sub
        
No_Find:
        Select Case Err.Number
               Case 91
                    MsgBox "Select a valid option", vbQuestion, "Error"
                    Resume cb1_Exit
               Case Else
                    On Error GoTo 0
                    Resume
        End Select
           
End Sub

This reduces the size of the code you would need to write. I would however reconsider renaming your fields.

Regards,

Rich

Re: Change Part Of Cell Content

Hi Dave,

Thanks for answering so promptly.

What you say is quite true, however, I am trying to "streamline" data entry so that the user (ie me....) can keep bashing away without having to keep moving to the mouse, double clicking etc etc. The natural direction of the input is straight down the sheet, hence the use of the enter key.

Essentially I'm designing a Technical design work book which keeps track of the tech design of large programs, including the development times etc etc. Part of the process is typing in the PDL of the method that is being designed. This requires indentation in places and having to do that manually is a chore. The PDL eventually turns into the comments of the program, with the real code being filled in underneath.

So now, dependant on the code in the row above, the spreadsheet automatically indents the following row, placing the cursor at the end of the indent ready for the next line of PDL.

The work book currently interfaces with SAP to get domain and data element declarations that may be mentioned and subsequently will then upload any custom declarations that have been made in the PDL, saving a lot of time and bother.

Thats the reason why I wanted to be able to get Excel to 'pre-populate' a cell and then enable me to add stuff to it without removing the previous contents when the cell is entered either after using the enter key or one of the cursor keys.

I must admit, I did try searching here before I entered my original post but must have been using the wrong search words. Your system did pick up on my mention of F2 though and provided me with an answer.

You say that SendKeys is a hack, which is fair enough. Is there another way of doing this ? For example possibly by changing the edit mode of the cell (I'm not sure of the right terminology) from "Selected" to "Append" ??

Regards

Rich

Hi.

In Excell 2000, when you enter a cell by using either the cursor keys or the enter key, the contents of that cell are selected. Typing anything other than the 'F2' key deletes the current entry in the cell, replacing it with what you are typing. This is not the same as double clicking on a cell whereby the cell does not have any of it's contents selected so that when you type the keys are inserted at the cursor position

Is there any way in which this behaviour can be changed so that the first and subsequent characters typed after entering the cell are added to the end of the current contents of the cell ?

Thanks,

Regards

Rich

Resolved......

Application.SendKeys ("{F2}")

Re: Open Tab Delimited File Via Menu Losing Data

Quote from Dave Hawley

Kind of hard to tell when you sample doesn't even contain the offending line, don't you think?

It does.....

'03 '0623 [div class="help"]Open the file using Excel and arrange the columns in the best format for yourself - either word wrapped or just one single line. (Some of the lines are quite long so wrapping the text is the best). Enter the translated text in the next adjacent column which is column E.[br][br]You will also find that there is HTML embedded in the text in various places (for example this help text has div's all over the place). Due to the semantics of your language you may have to move these but please do not alter them![br][br]The columns of the file are:[ul][li]Column 'A': The record type for the row.[/li][li]Column 'B': Screen name or message number.[/li][li]Column 'C': Screen field or help paragraph number.[/li][li]Column 'D': The text[/li][/ul]Dependant on the record type the row may then contain:[ul][li]A Record Header or Footer[/li][li]Character Set Information[/li][li]Screen texts[/li][li]Help Texts[/li][li]Messages[/li][li]or Http Targets[/li][/ul][/div]

(I've replace all the chevrons with square brackets so it does not cause problems)

Regards

Rich[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

Quote from Parsnip

Hi Rich

Opens up correctly for me with no obvious truncation (ie text in Notepad is the same as in Excel), but I am using xl2003 so maybe that's a difference?

Richard

Quite a big one I'd say, but it's strange.... Anyone else out there got 2000 ?

Rich

Hi,

I have a puzzling problem. The attached file is contains a single record from a much larger tab delimited text file. When I attempt to open this file in Excel using File->Open and select 'Delimited', then on the next screen, make sure Tab is selected then click finish, this particular line is truncated immediately after the 'next' at 'next adjacent'.

I have checked the text with a hex editor to see if there are any control characters there but nope:

[FONT="Courier New"]0C69:0200 6E 20 74 68 65 20 6E 65-78 74 20 61 64 6A 61 63 n the next adjac[/FONT]

There are much longer lines than this in the file that load correctly. Any one have any ideas why this is happening ?

The file itself is a list of texts used in a web site that are being made available for translation. The format is Record_type, Message Number, empty, Message Text.

Please note that the text file does contain HTML tags.

I'm using Excel 2000 (9.0.2720)

Regards

Rich

Re: Vb Code Freezing At Line Input Command

The way I would do this would be to implement a buffer and read chunks of the file into the buffer using the get command and work from the buffer.

I would also try and parse the line using a loop rather than straight line code, reading from the front of the buffer and getting another block of data using the get statement when the length of the buffer falls below a certain limit.

Using the get command allows you to control how much data you read at one time. The upper limit for a single string variable is a lot lower that 120mb!

Regards

Rich

Re: Protected Sheet - Macro To Unprotect, Refresh Sql Query & Re-protect Fails

Firstly, a point about coding style.

Always include an 'OPTION EXPLICIT' statement at the top of your code modules. This forces you to declare all your variables before use and can stop a lot of potential errors caused by typing mistakes.

Secondly try the protection without using a password first (ie uncomplicate things). I use this all the time in my code and it works without problem:

'*
'***************************************************************************
'*
'* Sub:      Change_Sheet
'*
'* Purpose:  Flags the fact that the sheet is being changed programatically
'*           so that events do not get triggered by the changes
'*
'* Entry:    None
'*
'* Exit:
'*
'* Calls:
'*
'***************************************************************************
'*
Sub Change_Sheet()
    Change_Stack(StackPtr).Changing = g_Changing
    Change_Stack(StackPtr).Update = Application.ScreenUpdating
    StackPtr = StackPtr + 1
    If g_Changing = False Then
       g_Changing = True
       ActiveSheet.Unprotect
       Application.ScreenUpdating = False
       Application.EnableEvents = False
    End If
    Call Push_Status("Busy")
End Sub


'***************************************************************************
'*
'* Sub:      Lock_Sheet
'*
'* Purpose:  Locks the sheet for changes
'*
'* Entry:
'*
'* Exit:
'*
'* Calls:
'*
'***************************************************************************
'*
Sub Lock_Sheet()
    StackPtr = StackPtr - 1
    g_Changing = Change_Stack(StackPtr).Changing
    Application.ScreenUpdating = Change_Stack(StackPtr).Update
    If g_Changing = False Then
       ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
       Application.EnableEvents = True
    End If
    Call Pop_Status
End Sub

Regards

Rich

Re: If Statement Query

Quote from JA1

or more readable and thus more maintainable

Code

Select Case Sheet1!W13
Case "0,", "00,", "  ", "    "
    strReturn ="No;" & vbCrLf
Case Else
    strReturn =""
End Select

Regards,

J.

Display More

Or even more readable:

Select Case Sheet1!W13
Case "0,", "00,", Space(2), Space(4)
    strReturn ="No;" & vbCrLf
Case Else
    strReturn =""
End Select

Regards

Rich

Re: Error In Macro With Text Functions

It's to do with the references you have defined and the order in which they are defined.

Goto VBE->Tools->References and make sure:

Visual Basic For Applications
Microsoft Excel 9.0 Object library (or greater)
Office 9.0
OLE Automation

Are checked.

Regards

Rich

Re: Access File From Workgroup Through Macro

A workgroup is only a collection of computers that are joined in a peer to peer network.

You need to create a shared folder on the computer where the file is located. If people will be changing the file make sure that have the rights to change data (the second check box in the file sharing dialog).

Then, making sure that all the computers in the work group have different names, yoiu can access the file in the same way: \\computer_name\dir_path\file.xls.

Regards

Rich

Re: Unwanted Characters Converting To Text Format

Quote from douglash

aaaah, yeah, thanks alot HTH... i can't believe i didn't think of it, but i will next time... thanks again... :p

Unless one of the guys who know's Excel better than me can suggest a better way, I would think you would need a macro to do it, or if you're not up to writing that, and transferring the file is not a regular occurance, then Ctrl-H I'm afraid.

What option are you using to convert the file ? CSV, TAB, TXT, MSDOS TXT ??

Regards

Rich

Re: Unwanted Characters Converting To Text Format

This is to do basically with CSV. Excel is making it safe to import into another program as comma's unless enclosed in quotes tend to be used as field delimiters. This means that if there is a comma in a text string, unless it is in quotes, it will be split into two fields at the comma..

It seems to do that for all the text conversions, which is a bit naff.....

Regards,

Rich

Re: specifying a search with a forms box.

To Add an item to a combo box, use something like:

Found_List.AddItem (This_Worksheet.Name)

As an aside, you might also want to include a Soundex search in your code. This will return names that sound like the name typed in by the user.

Regards

Rich

Re: Parse Decimal Numbers From Text

Quote from minitman

The fix that Rich gave works but yours looks more finished (the masters touch).

Huh!

As a general note if you look at this routine as a very useful tool that you can use over and again, by putting the strings you are searching for into a constant if you need to include more characters, you just add them to the constant. The actual code does not change. The other thing is, by removing the constant and changing references to it to a sub routine parameter you have a routine that can be used to remove all sorts of stuff from a string.

By including them as clauses in the if statement this 'reusefulness' dissappears to a certain extent.

Regards

Rich

Re: Prevent Duplicates in Random Generation

Don,

I've had a look at your spreadsheet and it appears to be working correctly. Due to the unique names the names can only be selected once. I don't think the error is in the shuffle sheet or the technique that Dave gave you.

It's too late here to start debugging but I'll check tomorrow and if no one has answered, I'll have another look.

So where is USS Essex now or is that classified ?? :sailboat:

Regards

Rich

Re: Prevent Duplicates in Random Generation

Quote from Dave Hawley

Create a uniue list of the names.
Add this Formula in an adjacent column =RAND() and Copy down
Sort both columns by the random numbers.
Now use a simple reference formula where needed to each name in the list.

That's a neat trick. I'll remember that. It get's rid of the problem of when you get down to the last few entries and are using RND to select from an array.

Re: Control Macro With Times

Quote from PTG258

It could be an egg-boil timer, it does not matter what I use it for, does it ?

Actually it does.

For example if you were thinking about using a mutli user spreadsheet then you would have been told that you are starting out on an impossible task.

Secondly, people here may have a better idea of doing what you want to do.

Thirdly, what happens if you didn't realise the implications of a user being off line ?

So, whilst they may seem like dozy questions to you, it helps to give other people here an idea of what you're up to and propose more fitting solutions.

Regards

Rich

Oh.

Ps. If you want something as mission critical as an egg-timer, I wouldn't use Microsoft.

Re: Extractnumber Removes Decimal With Letters

Insert after the 'Sub' statement:

Const c_number_punctuation as string = "."

And replace

If IsNumeric(Mid(sText, iCount, 1)) Then

With

If IsNumeric(Mid(sText, iCount, 1)) or Instr(c_Number_Punctuation, Mid(sText, iCount, 1)) > 0 Then

To add extra characters to leave in the string, add them to the constant (for example the minus sign, the plus sign etc)

Regards

Rich

Re: Control Macro With Times

Quote from Dave Hawley

No idea what you mean by "then 45 seconds prior to that event the worker can be notified"

Try the OnTime Method

I think the OP is after a way of scheduling something or other. The time that this has to occur is known. The work to which that (task??) has been assigned internally and is being notified that he has to start that task.

A couple of questions though.

Is it really required that they start these tasks "on the dot" as it were ?? If not I would be tempted to use a worklist where the next job is added to the bottom of the list and the job to be serviced is taken from the top of the list.

Is this a spreadsheet whereby all the users have to have this sheet open to work, or is this a standalone spreadsheet used by one person alone ? If this is a multi-user set up then really the best answer is find another way because excel and multi-users don't mix (there are plenty of threads on this and the reason why)

Lastly, what happens if the user does not have excel open at the time he is due to start the task ?

Regards

Rich

Re: Use Vba To Create Custom Toolbar

A while ago, I came up with this:

Sub Set_Button(Button As CommandBarButton, Image As String, Caption As String, faceId As Integer, Tooltip As String, Macro As String)
    '*
    '* Change the button face ?
    '*
    Dim Image_Object As Object
    If Image <> "" Then
       Set Image_Object = Sheets(1).Shapes(Image)
       With Image_Object
            .Copy
            .Visible = False
       End With
       Set Image_Object = Nothing
    End If
    With Button
         .Style = msoButtonIcon
         .Caption = Caption
         .faceId = faceId
         If Image <> "" Then
            .PasteFace
         End If
         .TooltipText = Tooltip
         .OnAction = Macro
    End With
End Sub
'*
'***************************************************************************
'*
'* Sub:      Show_Toolbar
'*
'* Purpose:  Displays the application tool bar,  or creates and displays if
'*           it does not exist
'*
'* Entry:    None
'*
'* Exit:
'*
'* Calls:
'*
'***************************************************************************
'*
Sub Show_Toolbar()
    Dim CmdBar As CommandBar
    Dim Dupe_Button  As CommandBarButton
    Dim Sap_Button As CommandBarButton
    Dim New_Button As CommandBarButton
    Dim CUsr_Button As CommandBarButton
    Dim Del_Button As CommandBarButton
    Dim Add_Button As CommandBarButton
    '*
    On Local Error GoTo wa_error
    If ActiveSheet.Name = "Order Entry" And Characteristic_Entry.Visible = False Then
       Application.CommandBars(c_toolbar).Visible = True
    End If
wa_exit:
    On Local Error GoTo 0
    Exit Sub
wa_error:
    Select Case Err.Number
           Case 5
                '*
                '* No Tool bar - Create it.
                '*
                Call Change_Sheet
                '*
                '* Make the button pictures visible so that we can select and copy them
                '*
                Sheets(1).Shapes(c_icon_delete_lines).Visible = True
                Sheets(1).Shapes(c_icon_submit_order).Visible = True
                Sheets(1).Shapes(c_icon_insert_line).Visible = True
                Sheets(1).Shapes(c_icon_dup_line).Visible = True
                Sheets(1).Shapes(c_icon_clear_order).Visible = True
                '
                Set CmdBar = Application.CommandBars.Add
                CmdBar.Name = c_toolbar
                With CmdBar.Controls
                     Set Sap_Button = .Add(msoControlButton)
                     Set Add_Button = .Add(msoControlButton)
                     Set Del_Button = .Add(msoControlButton)
                     Set New_Button = .Add(msoControlButton)
                     Set Dupe_Button = .Add(msoControlButton)
                     Set CUsr_Button = .Add(msoControlButton)
                End With
                '*
                '* Set the new button's properties
                '*
                Call Set_Button(Del_Button, c_icon_delete_lines, "Delete Lines", 53, "Deletes the current order line", "Start_Delete_Lines")
                Call Set_Button(Sap_Button, c_icon_submit_order, c_Submit_Order_Caption, 53, "Submit the order to SAP", "Start_Check_And_Go")
                Call Set_Button(Add_Button, c_icon_insert_line, "Insert Line", 53, "Inserts a line into the order", "Start_Add_Line")
                Call Set_Button(Dupe_Button, c_icon_dup_line, "Duplicate Lines", 53, "Duplicate Order Lines", "Start_Dup_Lines")
                Call Set_Button(New_Button, c_icon_clear_order, "Clear Order", 459, "Clears the current Order", "Start_Clear_Order")
                Call Set_Button(CUsr_Button, "", c_Change_User_Caption, 327, "Change the current SAP user", "Start_Change_User")
                Call Lock_Sheet
                Resume
           Case Else
                On Local Error GoTo 0
                Resume
    End Select
End Sub
Sub Hide_Toolbar()
    On Local Error GoTo wd_error
    Application.CommandBars(c_toolbar).Visible = False
wd_exit:
    On Local Error GoTo 0
    Exit Sub
wd_error:
    Resume wd_exit
End Sub

The icons you want to use on the tool bar must be stored as hidden images in the spreadsheet somewhere.

Then,

In the workbook open and some other events:

Call Show_Toolbar


Private Sub Workbook_Deactivate()
        Call Hide_Toolbar
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
        Call Show_Toolbar
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
        Call Hide_Toolbar
End Sub

Hope that gives you some ideas.

Regards

Rich