Solved

How to modify a popup form in Excel 2003

Posted on 2013-06-27
21
207 Views
Last Modified: 2013-07-16
MartinLiss was really helpful to give me a code to get a form to pop up. After i closed the question, i realized one more thing. Here's the code:

Sub ShowDataForm()
    Dim lngLastRow As Long
       
    lngLastRow = Range("A65536").End(xlUp).Row
   
    Range("A2:E" & lngLastRow).Select
    ActiveSheet.ShowDataForm
End Sub

On the columns A-E, I acutally do't want column C to show up on the form, how would i change the code to show columns A, B, D and E?
0
Comment
Question by:brasiman
  • 11
  • 9
21 Comments
 
LVL 6

Expert Comment

by:Michael
ID: 39283045
Hi brasiman,

there are probably more elegant ways, but what you could do is this: hide the column before the dataform shows up. And unhide when you close the dataform.
Your macro code would then look like this:
Sub ShowDataForm()
    Dim lngLastRow As Long
       
    lngLastRow = Range("A65536").End(xlUp).Row
    
    Columns("C:C").EntireColumn.Hidden = True
    Range("A2:E" & lngLastRow).Select
    ActiveSheet.ShowDataForm
    Columns("C:C").EntireColumn.Hidden = False
End Sub

Open in new window

0
 

Author Comment

by:brasiman
ID: 39283061
Hi JazzyJoop. Thanks for the quick reply. Column C still shows when i do that code. :(
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39283070
    Sub ShowDataForm()
        Dim lngLastRow As Long
        Dim r As Range
        
        
        lngLastRow = Range("A65536").End(xlUp).Row
        
        Set r = Range("A2:A" & lngLastRow)
        Set r = Application.Union(r, Range("B2:B" & lngLastRow))
        Set r = Application.Union(r, Range("D2:D" & lngLastRow))
        Set r = Application.Union(r, Range("E2:E" & lngLastRow))
        
        ActiveSheet.ShowDataForm
    End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39283083
Scratch that it seems that it's not possible with a data form.
.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39283094
I've got an idea. Be back soon:)
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 39283112
Yes!

    Sub ShowDataForm()
    Dim lngLastRow As Long
    Dim r As Range
    
    
    lngLastRow = Range("A65536").End(xlUp).Row
        
    ' Hide the actions going on
    Application.ScreenUpdating = False
    
    ' Temporarily move column C to column K
    Columns("C:C").Select
    Selection.Cut
    Columns("K:K").Select
    ActiveSheet.Paste
    
    ' Delete col C
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    
    ' Select the report data and show the report
    Range("A2:D" & lngLastRow).Select
    ActiveSheet.ShowDataForm
    
    ' Put the saved col C data back where it belongs
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Columns("K:K").Select
    Selection.Cut
    Columns("C:C").Select
    ActiveSheet.Paste
    
    Application.ScreenUpdating = True

    End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39283113
Note that I arbitrarily chose column K to store column C data, so if you have anything in that column then change all the Ks to some other letter.
0
 

Author Comment

by:brasiman
ID: 39284567
I get an error when i run it. When I click Debug, it takes me to Visual Basic and has ActiveSheet.Paste highlighted in yellow. Its the one under:

' Temporarily move column C to column K
    Columns("C:C").Select
    Selection.Cut
    Columns("K:K").Select
    ActiveSheet.Paste

Also, I just found out I need to hide columns C and D, so on the entry form only A, B and E show up.
0
 

Author Comment

by:brasiman
ID: 39284607
Ok, I guess she needs a few things. Here they are:
1. Still have columns A-E. Columns C and D she doesn't want on the form.
2. She does have data in columns F-P, that she doesn't want on the form.

So, with that said, how would we modify the code? I do get a runtime error saying ShowDataForm method of Worksheet class failed. When i click debug, it highlights ActiveSheet.Paste. I did try changing the K to Q, but i still get the mentioned error.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39284666
It worked for me and so does this where I made your requested modification and also changed the name of the macro from ShowDataForm to DoReport to avoid confusion between the macro name and the native Excel function called ShowDataForm.
DataEntry.xls
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:brasiman
ID: 39284853
Thats weird. Ok, i'll try it again.
0
 

Author Comment

by:brasiman
ID: 39284976
So it looks like that will work. But when i insert cells at the top to give me more room, it errors out. So i changed the code to A6 instead of A2, but still get the error.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39284988
Please post the workbook as you have it now.
0
 

Author Comment

by:brasiman
ID: 39285000
Here it is.
DataEntry--3-.xls
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39285067
I'm getting an error that I don't understand yet but I have to leave for a few hours. I'll get back to you.
0
 

Author Comment

by:brasiman
ID: 39285071
Ok, thank you! I appreciate the heads up on timing so i don't just wait. :) Thanks!
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 39285816
This works for me.

    Sub DoReport()
    Dim lngLastRow As Long
    Dim r As Range
    Dim lngFirstRow As Long
    Dim lngRow As Long
    
    lngLastRow = Range("A65536").End(xlUp).Row
    For lngRow = 1 To 1000
        If Cells(lngRow, 1) <> "" Then
            lngFirstRow = lngRow
            Exit For
        End If
    Next
        
    ' Hide the actions going on
    Application.ScreenUpdating = False
    
    With ActiveSheet
        ' Temporarily move column C & D to column K & L
        .Columns("C:D").Select
        Selection.Cut
        .Columns("K:L").Select
        .Paste
        
        ' Delete col C & D
        Columns("C:C").Select
        Selection.Delete Shift:=xlToLeft
        ' This isn't a mistake. Old column D is now C
        .Columns("C:C").Select
        Selection.Delete Shift:=xlToLeft
        
        ' Select the report data and show the report
        Range("A4:C" & lngLastRow).Select
        
        ' Select the data for the report
        .Range("A" & lngFirstRow & ":C" & lngLastRow).Select
        Range(Selection, Selection.End(xlDown)).Select
        ' For some resaon this is required
        Selection.Name = "Database"
        
        .ShowDataForm
        
        ' Put the saved col C data back where it belongs
        .Columns("B:B").Select
        Selection.Insert Shift:=xlToRight
        .Columns("B:B").Select
        Selection.Insert Shift:=xlToRight
        .Columns("K:L").Select
        Selection.Cut
        .Columns("B:C").Select
        .Paste
    End With
    Application.ScreenUpdating = True

    End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39288229
Did it work for you?
0
 

Author Comment

by:brasiman
ID: 39330449
That worked. Thank you for your help!!
0
 

Author Closing Comment

by:brasiman
ID: 39330460
You're awesome MartinLiss. Thanks for your help and patience with me. Its working great.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 39330480
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now