Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to modify a popup form in Excel 2003

Posted on 2013-06-27
21
Medium Priority
?
222 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 49

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 49

Expert Comment

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

Expert Comment

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

Assisted Solution

by:Martin Liss
Martin Liss earned 2000 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 49

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 49

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 49

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 49

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 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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 49

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

597 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