Userform VBA questions

MICHBROWNAOL

Board Regular
Joined
Mar 15, 2003
Messages
55
I'm having a problem with my code for a userform, although the code problem isn't the user form itself, it's copying formats and formulas to the row where the userform data is placed in Excel.

What I am trying to do is rather simple - a user enters a series of data on the user form (either numbers or selections from listboxes, and clicks OK. The user form data is then placed in the appropriate cells on the next blank row of the spreadsheet. This part works fine.

The challenge that I'm having is that I then need to apply formating and insert formulas to the new row of data. I currently have a range named "FuncFormat" which contains the formulas and properly formated cells. I need to figure out how to copy this to the new range.

The other challenge I'm having is that I need my user to be able to do a sort of the entries to put them in date order. I've created a range and a macro to do this, however I need the range to continue to expand as the user adds new entries. What's the easiest way to accomplish this?

Here's my code so far:

Private Sub BookButton_Click()
Sheets("Functions").Activate
nextrow = Application.WorksheetFunction.CountA(Range("A:A"))
ActiveCell.Select
Range("funcformat").Copy Range("NEXTROW")
Cells(nextrow, 1) = FName.Text
Cells(nextrow, 2) = Fdate.Value
Cells(nextrow, 4) = Ftime.Value
Cells(nextrow, 5) = FGTD.Value
Cells(nextrow, 6) = FMealT.Text
Cells(nextrow, 7) = FBarT.Text
Cells(nextrow, 8) = FRoom.Text
Cells(nextrow, 9) = FWaitStaff.Value
Selection.Insert shift = xlDown

Call bookrevenue
End Sub


Private Sub bookrevenue()
Sheets("Revenue").Activate
nextrow = Application.WorksheetFunction.CountA(Range("A:A"))
Cells(nextrow, 1) = FName.Text
Cells(nextrow, 2) = Fdate.Value
Cells(nextrow, 3) = FGTD.Value
Cells(nextrow, 4) = FMealT.Text
Cells(nextrow, 5) = FFRev.Value
Cells(nextrow, 6) = FBRev.Value
Cells(nextrow, 7) = FRmFee.Value
Sheets("Functions").Activate
Unload Me
End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

MICHBROWNAOL

Board Regular
Joined
Mar 15, 2003
Messages
55
I'm sure this is possible... just need to figure out the right sequences for the VBA?

:rolleyes:
 

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi, I presume your having trouble with this line ...
Range("funcformat").Copy Range("NEXTROW")

Code:
Private Sub BookButton_Click()
Dim NextRow As Long

'You dont actually need to activate the sheet to accomplish this
'but it will require some small changes so Ill leave as is so not to
'confuse you.
Sheets("Functions").Activate

'NextRow = Application.WorksheetFunction.CountA(Range("A:A"))
'CountA may not work well if theres gaps in the data. Try this instead...
NextRow = Range("A65536").End(xlUp).Offset(1, 0).Row
'ActiveCell.Select   'You dont need this

Range("funcformat").Copy 'Is the range funcformat 1 row x 9 columns?
Range(Cells(NextRow, 1), Cells(NextRow, 9)).PasteSpecial xlPasteFormats
Cells(NextRow, 1) = FName.Text
Cells(NextRow, 2) = Fdate.Value
Cells(NextRow, 4) = Ftime.Value
Cells(NextRow, 5) = FGTD.Value
Cells(NextRow, 6) = FMealT.Text
Cells(NextRow, 7) = FBarT.Text
Cells(NextRow, 8) = FRoom.Text
Cells(NextRow, 9) = FWaitStaff.Value
'Selection.Insert shift = xlDown  'You dont need this

Call bookrevenue
End Sub
 

Forum statistics

Threads
1,148,530
Messages
5,747,238
Members
424,070
Latest member
smanni3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top