Solved

How to modify a popup form in Excel 2003

Posted on 2013-06-27
21
215 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 47

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 47

Expert Comment

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

Expert Comment

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

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 47

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 47

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
 

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 47

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 47

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 47

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 47

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 47

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

737 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