How to modify a popup form in Excel 2003

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?
brasimanAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
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
 
MichaelBusiness AnalystCommented:
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
 
brasimanAuthor Commented:
Hi JazzyJoop. Thanks for the quick reply. Column C still shows when i do that code. :(
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Martin LissOlder than dirtCommented:
    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
 
Martin LissOlder than dirtCommented:
Scratch that it seems that it's not possible with a data form.
.
0
 
Martin LissOlder than dirtCommented:
I've got an idea. Be back soon:)
0
 
Martin LissOlder than dirtCommented:
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
 
Martin LissOlder than dirtCommented:
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
 
brasimanAuthor Commented:
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
 
brasimanAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
brasimanAuthor Commented:
Thats weird. Ok, i'll try it again.
0
 
brasimanAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
Please post the workbook as you have it now.
0
 
brasimanAuthor Commented:
Here it is.
DataEntry--3-.xls
0
 
Martin LissOlder than dirtCommented:
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
 
brasimanAuthor Commented:
Ok, thank you! I appreciate the heads up on timing so i don't just wait. :) Thanks!
0
 
Martin LissOlder than dirtCommented:
Did it work for you?
0
 
brasimanAuthor Commented:
That worked. Thank you for your help!!
0
 
brasimanAuthor Commented:
You're awesome MartinLiss. Thanks for your help and patience with me. Its working great.
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.