Results 1 to 6 of 6

Thread: Input macros

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Youngstown, Ohio, USA
    Posts
    705
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Input macros

    We have a few legacy files in Lotus that, for the time being, we are unable to convert into Excel spreadsheets. The problem with these files is that they rely heavily on macros for data input, and we have no idea how to re-write these macros in Excel.

    One of the macros goes like this: An Autoexec macro opens up a menu, and the menu branches into different subroutines for input, print, save, etc. This probably wouldn't be too difficult to recreate in VBA. The more troublesome part of the macro is the input routine. The existing macro moves the cursor to a specific cell and pauses to allow input into that cell; hitting Enter moves to the next cell, and the process repeats. Once the entire input process has run through, the macro returns control to the main menu. The input cells are not necessarily contiguous, though many are.

    Nothing I have been able to find points to a ready solution for re-creating the input routine in VBA. This even includes checking the text books used in a VBA course at the local university. <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    Unfortunately, no one in the office has any great degree of expertise with Excel macros, other than occasional use of the macro recorder.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input macros

    I got round this one by using Inputbox - repeated calls and put the data returned from Inputbox into the cells in question. It works, and to the best of my knowledge is the only way to go, but I wouldn't want to have to use it for large data ranges. Have you considered migrating all the input cells to a separate sheet which is completed manually, then using code to copy the data across when the rest of your code runs?

    You say you don't have much experience with VBA - try running the following in a workbook with a sheet renamed to data input and see if you could live with it.

    Sub trial()
    ActiveWorkbook.Sheets("datainput").Cells(1, 1).Value = InputBox("enter cell a1 data")
    ActiveWorkbook.Sheets("datainput").Cells(1, 2).Value = InputBox("enter cell b1 data")
    ActiveWorkbook.Sheets("datainput").Cells(1, 3).Value = InputBox("enter cell c1 data")
    End Sub

    Brooke

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input macros

    If the user _has_ (*) to select the cell then the only way I came accross is from J-Walk Associates. As their site is down (**) here the cached page
    <A target="_blank" HREF=http://www.google.com/search?q=cache:www.j-walk.com/ss/excel/tips/tip81.htm>http://www.google.com/search?q=cache:www.j.../tips/tip81.htm</A>

    (*) i.e. address is not constant
    (**) I really hope it is not discontinued, its a stellar site.

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input macros

    <A target="_blank" HREF=http://www.j-walk.com>jwalk</A> is up and running at the moment

    Brooke

  5. #5
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input macros

    Although I prefer Brooke's solution above, you could protect all cells apart from those required for data entry and make sure Tools | Options | Edit | 'Move Selection after Entry' is selected.
    Tab (and Shift-Tab) will let you move through the cells otherwise.
    An 'OnEvent' (?) macro could capture the change in the last cell for data entry and take you on to the next stage. . .

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Input macros

    This will not be perfect for your particular case, but it points you in a possible direction.

    I assumed here that the worksheet has the name "Blad1" (which is "Sheet1" in Dutch) and that the cells (1,1), (5,1) and (8,1) are empty and are the cells that need some input from the user. In the workbook_open event I protect the complete sheet and the only cell that allows data entry is cell (1, 1). By opening the workbook, the user is prompted to enter data in that cell. As soon as this is done, the SheetChange event is launched. If you entered data in cell(1,1) on sheet "Blad1" then the worksheet is protected and only in cell (5,1) the user is allowed to enter data (eventually via the inputbox). Then the same is repeated for cell (8,1). When this is finished, the worksheet is unprotected and control is back to the user.

    Option Explicit
    Private Sub Workbook_Open()
    ActiveWorkbook.Sheets("Blad1").Cells(1, 1).Locked = False
    ActiveWorkbook.Sheets("Blad1").Cells(1, 1).FormulaHidden = False
    ActiveSheet.Protect
    ActiveWorkbook.Sheets("Blad1").Cells(1, 1).Select
    ActiveWorkbook.Sheets("Blad1").Cells(1, 1).Value = InputBox("Enter data")
    End Sub

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Blad1" Then
    If Target = Cells(1, 1) And IsEmpty(Cells(1, 1)) = False Then
    ActiveSheet.Unprotect
    ActiveWorkbook.Sheets("Blad1").Cells(1, 1).Locked = True
    'assume the next cell for input is cell 5,1
    ActiveWorkbook.Sheets("Blad1").Cells(5, 1).Locked = False
    ActiveWorkbook.Sheets("Blad1").Cells(5, 1).FormulaHidden = False
    ActiveSheet.Protect
    ActiveWorkbook.Sheets("Blad1").Cells(5, 1).Select
    ActiveWorkbook.Sheets("Blad1").Cells(5, 1).Value = InputBox("Enter data")
    End If
    If Target = Cells(5, 1) And IsEmpty(Cells(5, 1)) = False Then
    ActiveSheet.Unprotect
    ActiveWorkbook.Sheets("Blad1").Cells(1, 1).Locked = True
    'assume the next cell for input is cell 8,1
    ActiveWorkbook.Sheets("Blad1").Cells(8, 1).Locked = False
    ActiveWorkbook.Sheets("Blad1").Cells(8, 1).FormulaHidden = False
    ActiveSheet.Protect
    ActiveWorkbook.Sheets("Blad1").Cells(8, 1).Select
    ActiveWorkbook.Sheets("Blad1").Cells(8, 1).Value = InputBox("Enter data")
    End If
    'etc.
    ActiveSheet.Unprotect
    End If
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •