Hi does anybody know how to create a custom form that user can fill in excel.

what i need is: lets say user select and click 1 or 3 cell a popup form open.

The user then fills in the info on the form ( ex.. name, address, tour operator name, arival date, departure date,ect)

then they save it.

That data is then inserted into the cell or cells that was selected.

Im not an expert in excel but im sure this must be possible with vb.

Thanks

Hi eltonpiko, yes this is very possible but I would encourage you to do half the work first. For instance, create the userform based off of your exact requirements. The coding can be done by myself or someone else but the layout needs to be done by you because only you know your exact requirements. If you need help with creating a userform, I would suggest using Youtube, you can see a help video here. Like I said, set it up and then I'll help with the code.

ok thank you very much stuugie. I shall create the layout for the userform and let you know so you can help me out with the coding

Hi stuugie ive design the vb form you can see in the attachment and also the name of the fields.
44b8106a38970a6dea8e63487fd11fdf
now the form should popup and insert that data ! in the number of field selected can you help me out with the code.

thank you very much

Hi stuugie ive manage to code the form and add the form to a marco and make it available in right click menu. Now the data is being inserted into respective cell without any problem.

The only thing im still getting a bit of trouble to do is, fill the cell with a certain colour base on type of client.

exp.. if i select direct client the selected cell will be red if was from a dmc the cell will be green and so on

Thanks

Comments
Nice job working this one out!

Nice job coding it all to your requirements, it's not that hard once you get on a roll.

As for colours, I've found that the best way to deal with colours is to record a macro where you change cell colours a whole bunch of times. Once that is done, you can code the colours easily. For example, I have the following that I reference a fair bit:

Sub ColourSchemes1()
'
' ColourSchemes Macro
' Recorded in order to know the correct numbers for needed colours.
'

'

    'Dark Red
    Range("A17").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    'Red
    Range("A18").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    'Orange
    Range("A19").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    'Orange
    Range("A20").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    'Yellow
    Range("A21").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    'Light Green
    Range("A22").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    'Green
    Range("A23").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    'Light Blue
    Range("A24").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 15773696
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    'Blue
    Range("A25").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    'Dark blue
    Range("A26").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 6299648
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    'Purple
    Range("A27").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10498160
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    'White
    Range("A28").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    'No colour
    Range("A29").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Let me know if this helped.

Hi thanks for the reply but i manage to do it with another solution i added below.

But the problem im facing with this, is that only one cell is beign coloured. I need that if i select three cells when i submit that data from my form the 3 cells are coloured.

Private Sub submit_Click()
ActiveCell.Value = fullname.Value & " , arrival Date: " & arrival.Value & " , Departure Date: " & departure.Value & " , children age: " & age.Value & " , Client type: " & clienttype.Value & " , DMC: " & dmc.Value & " , Payment Type: " & paymenttype.Value & " , Payment Facility: " & paymentfac
With ActiveCell.Interior
        Select Case clienttype.Value

            Case "Educational"
                .Color = 22
            Case "Tour Operator"
                .Color = 8
            Case "Family Trip"
                .Color = 15
            Case "DMC'S"
                .Color = 10
            Case "Honeymooners"
                .Color = 20
        End Select
    End With
Unload clientform
End Sub

user will select the amount of cells they required with their mouse cursors.it can be any amount but the amount selected need to be colourd accordingly.

Thanks a lot stuugie it works that did the trick. Now i just need to figure out the colour codes.

Is there anyway in excel to prevent long text in cells overlap non selected cell?

I think you mean to autofit your cells right? Insert the following into a regular module:

Sub AutofitCells()
    Dim oC As Range
    Dim aSht As Worksheet

    Set aSht = ActiveSheet
    'Go through all the columns that have text in their headers
    'and autofit them
    'Code assumes there are headers in row 1
    For Each oC In aSht.Range(aSht.Cells(1, 1), aSht.Cells(1, aSht.Columns.Count).End(xlToLeft))
        oC.EntireColumn.AutoFit
    Next oC

End Sub

and then call it from your form, after all the cell manipulation is complete.

Ok that works, thanks a lot. can you tell me how can i make a combobox appear or activate based on another combobox result.

Example if i select payment type as cash then only will a combobox for currency be displayed or gets activated.

Sure, wherever you want the new userform to be called you just enter

Userform.Show

Where Userform is the actual name of the form (like UserForm1)

Sorry, I misread your question. You'll have to define what you mean by appear or activate. What I usually do is disable comboboxes in the userform initialize procedure and then enable them when I need to. For instance, combobox2 has been disabled in the initialize event and then enabled in the combobox change event, something like:

Private Sub UserForm_Initialize()
    Me.ComboBox2.Enabled = False
End Sub

Private Sub ComboBox1_Change()
    Me.ComboBox2.Enabled = True
End Sub

I managed to do it like this instead

Private Sub paymenttype_Click()

If paymenttype.Value = "Cash" Then

tcurrency.Enabled = True
culabel.Enabled = True

Else

tcurrency.Enabled = False
culabel.Enabled = False

End If


End Sub

Thanks again for your greate help.

Yeah that looks good! There are so many ways to accomplish the same thing with VBA and that's why I like it.

since the form has a lot of data i think it would be a better idea to store all these data in a comment instead of in the cell.

how can i insert the userform value to a comment box?

the addcomment works with text how can i insert form value instead?

thanks

I'm not sure how to answer that specifically without knowing which you'll be drawing specific information from. However, I have a procedure that creates commments base on the values from another sheet so I'll show you that and maybe you'll be able to figure out your end just from reading mine. If not, we'll try again.

Sub RefreshComments()

    Dim wS1 As Worksheet, wsFL As Worksheet
    Dim oC As Range, fndRng As Range
    Dim cmt As Comment
    Application.ScreenUpdating = False

    Set wS1 = ActiveSheet: Set wsFL = Worksheets("Adminfin_Folder_Links")
    wS1.Cells.Select
    Selection.ClearComments
    wS1.Cells(1, 1).Select
    For Each oC In wS1.Range(wS1.Cells(2, 1), wS1.Cells(78, 13))
        If oC.Value <> "" Then
            Set fndRng = wsFL.Range(wsFL.Cells(1, 1), wsFL.Cells(100, 2)).Cells.Find(oC.Value, lookat:=xlWhole, matchbyte:=True)
            If Not fndRng Is Nothing Then
                With oC
                    .AddComment
                    .Comment.Text Text:="mvasas:" & Chr(10) & fndRng.Offset(0, 4).Value  'This is the important line for entering values into comments
                    .Comment.Visible = True
                    .Comment.Shape.Select True
                    With Selection
                        .AutoSize = True
                    End With
                    .Comment.Shape.Select False
                    .Comment.Visible = False
                End With
            End If
            Set fndRng = Nothing
        End If
    Next oC
    Application.ScreenUpdating = True

End Sub

Ok got it thanks a lot. I managed to do it like this

Private Sub submit_Click()

ActiveCell.Value = guestname.Value

With ActiveCell

.AddComment
.Comment.Text Text:="Number of Adult: " & noadults.Value & Chr(10) & "Date of Reservation: " & dateofreservation.Value & Chr(10) & "Number of children: " & nochildren.Value

End With
End Sub

Now just have to figure out how to make the comment box bigger.

Adapt the following:

Dim oComment As Comment

    Set oComment = ActiveCell.Comment
    With oComment
        .Visible = True
        .Text Text:="Number of Adult: " & noadults.Value & Chr(10) & "Date of Reservation: " & dateofreservation.Value & Chr(10) & "Number of children: " & nochildren.Value
        .Shape.TextFrame.AutoSize = True
    End With

ok got it, can set it like this

With ActiveCell

.AddComment
.Comment.Text Text:="Number of Adult: " & noadults.Value & Chr(10) & "Date of Reservation: " & dateofreservation.Value & Chr(10) & "Number of children: " & nochildren.Value
.Comment.Shape.Width = 350




End With

works very well now ill see if i can increase the font size also

Edited 2 Years Ago by eltonpiko: code error

Maybe:

Dim oComment As Comment

    Set oComment = ActiveCell.Comment
    With oComment
        .Visible = True
        .Shape.Select True
        With Selection.Font
            .Size = 12
        End With
        .Text Text:="Number of Adult: " & noadults.Value & Chr(10) & "Date of Reservation: " & dateofreservation.Value & Chr(10) & "Number of children: " & nochildren.Value
        .Shape.Width = 350
        .Visible = False
    End With

I found simple solution like this with fewer code

With ActiveCell

.AddComment
.Comment.Text Text:="Date of Reservation: " & dateofreservation.Value & Chr(10) & "Number of Adult: " & noadults.Value & Chr(10) & "Number of children: " & nochildren.Value & Chr(10) & "Age of Children: " & age.Value & Chr(10) & "Arrival date and time: " & arrivaldate.Value & ":" & arrivaltime.Value & Chr(10) & "Departure date and time: " & departuredate.Value & ":" & departuretime.Value & Chr(10) & "" & Chr(10) & "..................ROOMS INFORMATION.................." & Chr(10) & "" & Chr(10) & "Number of rooms: " & numberofrooms.Value & Chr(10) & "Type of rooms: " & typeofrooms.Value & Chr(10) & "Hotel Exclusivity: " & hotelexclusivity.Value & Chr(10) & "Extra Bed: " & extrabed.Value & Chr(10) & "Meal Plan: " & mealplan.Value & Chr(10) & "Rates Per Room Per Night: " & ratesperroom.Value & Chr(10) & "" & Chr(10) & "..................PAYMENT INFORMATION.................." & Chr(10) & "" & Chr(10) & "Type of Client: " & clienttype.Value _
& Chr(10) & "DMC OR TOUR OPERATOR: " & dmc.Value & Chr(10) & "Form of Payment: " & paymenttype.Value & Chr(10) & "Currency: " & tcurrency.Value & Chr(10) & "" & Chr(10) & "..................OTHER INFORMATION.................." & Chr(10) & "" & Chr(10) & "Booker: " & booker.Value & Chr(10) & "Confirmed By: " & confirmedby.Value & Chr(10) & "Confirmation Number: " & confirmationnumber.Value & Chr(10) & "Promo Code: " & promocode.Value & Chr(10) & "Special Remarks : " & specialremarks.Value



.Comment.Shape.Width = 350
.Comment.Shape.Height = 500

With .Comment.Shape.TextFrame
.Characters.Font.Size = 11
End With
.Comment.Shape.TextFrame.AutoSize = True
End With

Now that all good the last thing i need now is that my comment box has so many information in it.

when user open the comment they have to scroll down a lot to see the info and some parts gets cut off and if user mouse move from the trigger point the box closes and thats a problem. see photo below

49f3cb15d1c3fd474f8ee7d277eaed39

Is there a way i can make the comment box appear in the center of the screen each time it is triggered and even if mouse is moved from trigger point it remains open and can be closed by a close button?

There may be a way to do that but if there is it is beyond what I know how to do. Sorry. Personally though, I would take all that information that you want to display and dump it all back into a user form that can be moved and closed at the users will. Userforms are great for displaying user definged data. You could have the form populate based on the worksheet double click event, or something like that. In the meantime, I'll see what I can do with your comment box but as I said, that type of work is not something I've done before so I'm not sure.

I saw this post Click Here it suppose to have the solution to change the display position of the comment box but its not working for me. maybe there is somthing im doing wrong

This article has been dead for over six months. Start a new discussion instead.