Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > VBA - UserForm Reset Option
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default VBA - UserForm Reset Option
Here is my Userform that I created:

Sub Import()
'
' Import Macro
' Macro recorded 08/06/2008 by christopherr
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Y:\Financial Services\FS2008\Woodworkers\ER
Reports\052008\Test\Original Weyerhaeuser.TOC May 08.200806172100086.txt" _
, Destination:=Range("A1"))
.Name = "Original Weyerhaeuser.NelsonTrust.200806172055088"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Totals"
End Sub

Private Sub CommandButton1_Click()
Import
End Sub
Sub SubTotalERCode()
'
' SubTotalERCode Macro
' Macro recorded 08/06/2008 by christopherr
'

'
Selection.Subtotal GroupBy:=14, Function:=xlCount, TotalList:=Array(14), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=-42
Columns("M:M").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=42
End Sub

Private Sub CommandButton2_Click()
SubTotalERCode
End Sub
Sub Subtotals()
'
' Subtotals Macro
' Macro recorded 08/08/2008 by christopherr
'

'
ActiveWindow.SmallScroll Down:=-63
Range("I1").Select
Selection.Subtotal GroupBy:=13, Function:=xlSum, TotalList:=Array(9,
10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=81
End Sub
Private Sub CommandButton3_Click()
Subtotals
End Sub
Sub Worksheet()
'
' WorkSheet Macro
' Macro recorded 08/06/2008 by christopherr
'

'
Range("A2:N65").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Columns("E:E").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Sheets("Totals").Select
Application.CutCopyMode = False
End Sub

So macro sub Worksheet is selecting the data in range A2:A65.

I would like to have my user manually select the data range within the user
form.

I've researched this thread hoping to find similar suggestions/ideas, but
found none which is why I'm sending this request.

TIA
~Christopher

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default VBA - UserForm Reset Option
You need to use an inputbox with option 8. Here is the VBA help instructions

If Type is 8, InputBox returns a Range object. You must use the Set
statement to assign the result to a Range object, as shown in the following
example.

Set myRange = Application.InputBox(prompt := "Sample", type := 8)
If you don't use the Set statement, the variable is set to the value in the
range, rather than the Range object itself.



"megatron08" wrote:

Here is my Userform that I created:

Sub Import()
'
' Import Macro
' Macro recorded 08/06/2008 by christopherr
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Y:\Financial Services\FS2008\Woodworkers\ER
Reports\052008\Test\Original Weyerhaeuser.TOC May 08.200806172100086.txt" _
, Destination:=Range("A1"))
.Name = "Original Weyerhaeuser.NelsonTrust.200806172055088"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Totals"
End Sub

Private Sub CommandButton1_Click()
Import
End Sub
Sub SubTotalERCode()
'
' SubTotalERCode Macro
' Macro recorded 08/06/2008 by christopherr
'

'
Selection.Subtotal GroupBy:=14, Function:=xlCount, TotalList:=Array(14), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=-42
Columns("M:M").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=42
End Sub

Private Sub CommandButton2_Click()
SubTotalERCode
End Sub
Sub Subtotals()
'
' Subtotals Macro
' Macro recorded 08/08/2008 by christopherr
'

'
ActiveWindow.SmallScroll Down:=-63
Range("I1").Select
Selection.Subtotal GroupBy:=13, Function:=xlSum, TotalList:=Array(9,
10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=81
End Sub
Private Sub CommandButton3_Click()
Subtotals
End Sub
Sub Worksheet()
'
' WorkSheet Macro
' Macro recorded 08/06/2008 by christopherr
'

'
Range("A2:N65").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Columns("E:E").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Sheets("Totals").Select
Application.CutCopyMode = False
End Sub

So macro sub Worksheet is selecting the data in range A2:A65.

I would like to have my user manually select the data range within the user
form.

I've researched this thread hoping to find similar suggestions/ideas, but
found none which is why I'm sending this request.

TIA
~Christopher

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default VBA - UserForm Reset Option
Hi Joel,

So with the code that you listed below do I just insert into my macro named
sub worksheet?

I tried reviewing the code and couldn't find option 8.

Any suggestions/ideas would be great.

TIA
~Christopher

"Joel" wrote:

You need to use an inputbox with option 8. Here is the VBA help instructions

If Type is 8, InputBox returns a Range object. You must use the Set
statement to assign the result to a Range object, as shown in the following
example.

Set myRange = Application.InputBox(prompt := "Sample", type := 8)
If you don't use the Set statement, the variable is set to the value in the
range, rather than the Range object itself.



"megatron08" wrote:

Here is my Userform that I created:

Sub Import()
'
' Import Macro
' Macro recorded 08/06/2008 by christopherr
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Y:\Financial Services\FS2008\Woodworkers\ER
Reports\052008\Test\Original Weyerhaeuser.TOC May 08.200806172100086.txt" _
, Destination:=Range("A1"))
.Name = "Original Weyerhaeuser.NelsonTrust.200806172055088"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Totals"
End Sub

Private Sub CommandButton1_Click()
Import
End Sub
Sub SubTotalERCode()
'
' SubTotalERCode Macro
' Macro recorded 08/06/2008 by christopherr
'

'
Selection.Subtotal GroupBy:=14, Function:=xlCount, TotalList:=Array(14), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=-42
Columns("M:M").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=42
End Sub

Private Sub CommandButton2_Click()
SubTotalERCode
End Sub
Sub Subtotals()
'
' Subtotals Macro
' Macro recorded 08/08/2008 by christopherr
'

'
ActiveWindow.SmallScroll Down:=-63
Range("I1").Select
Selection.Subtotal GroupBy:=13, Function:=xlSum, TotalList:=Array(9,
10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=81
End Sub
Private Sub CommandButton3_Click()
Subtotals
End Sub
Sub Worksheet()
'
' WorkSheet Macro
' Macro recorded 08/06/2008 by christopherr
'

'
Range("A2:N65").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Columns("E:E").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Sheets("Totals").Select
Application.CutCopyMode = False
End Sub

So macro sub Worksheet is selecting the data in range A2:A65.

I would like to have my user manually select the data range within the user
form.

I've researched this thread hoping to find similar suggestions/ideas, but
found none which is why I'm sending this request.

TIA
~Christopher

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default VBA - UserForm Reset Option
I don't like using recorded macro without making changes. The recorded
macros use "Selection". I pefer to specify Worksheet names and Ranges.

Set myRange = Application.InputBox(prompt := "Sample", type := 8)
with Sheets("Sheet2")
MyRange.Copy
.Paste 'really shoud have a range here. Can cause errors.
.Columns("E:E").EntireColumn.AutoFit
.Columns("H:H").EntireColumn.AutoFit
.Columns("A:A").EntireColumn.AutoFit
end with

Here is my modifed code with your amcro

Sub Worksheet()
'
' WorkSheet Macro
' Macro recorded 08/06/2008 by christopherr
'

'
Set myRange = Application.InputBox(prompt := "Sample", type := 8)
MyRange.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Columns("E:E").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Sheets("Totals").Select
Application.CutCopyMode = False
End Sub


"megatron08" wrote:

Hi Joel,

So with the code that you listed below do I just insert into my macro named
sub worksheet?

I tried reviewing the code and couldn't find option 8.

Any suggestions/ideas would be great.

TIA
~Christopher

"Joel" wrote:

You need to use an inputbox with option 8. Here is the VBA help instructions

If Type is 8, InputBox returns a Range object. You must use the Set
statement to assign the result to a Range object, as shown in the following
example.

Set myRange = Application.InputBox(prompt := "Sample", type := 8)
If you don't use the Set statement, the variable is set to the value in the
range, rather than the Range object itself.



"megatron08" wrote:

Here is my Userform that I created:

Sub Import()
'
' Import Macro
' Macro recorded 08/06/2008 by christopherr
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Y:\Financial Services\FS2008\Woodworkers\ER
Reports\052008\Test\Original Weyerhaeuser.TOC May 08.200806172100086.txt" _
, Destination:=Range("A1"))
.Name = "Original Weyerhaeuser.NelsonTrust.200806172055088"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Totals"
End Sub

Private Sub CommandButton1_Click()
Import
End Sub
Sub SubTotalERCode()
'
' SubTotalERCode Macro
' Macro recorded 08/06/2008 by christopherr
'

'
Selection.Subtotal GroupBy:=14, Function:=xlCount, TotalList:=Array(14), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=-42
Columns("M:M").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=42
End Sub

Private Sub CommandButton2_Click()
SubTotalERCode
End Sub
Sub Subtotals()
'
' Subtotals Macro
' Macro recorded 08/08/2008 by christopherr
'

'
ActiveWindow.SmallScroll Down:=-63
Range("I1").Select
Selection.Subtotal GroupBy:=13, Function:=xlSum, TotalList:=Array(9,
10), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
ActiveWindow.SmallScroll Down:=81
End Sub
Private Sub CommandButton3_Click()
Subtotals
End Sub
Sub Worksheet()
'
' WorkSheet Macro
' Macro recorded 08/06/2008 by christopherr
'

'
Range("A2:N65").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Columns("E:E").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Sheets("Totals").Select
Application.CutCopyMode = False
End Sub

So macro sub Worksheet is selecting the data in range A2:A65.

I would like to have my user manually select the data range within the user
form.

I've researched this thread hoping to find similar suggestions/ideas, but
found none which is why I'm sending this request.

TIA
~Christopher

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default VBA - UserForm Reset Option
Thanks this is what I needed.

Can I create an Application.InputBox to prompt the user to find a file like
in the following macro? Is it even possible?

Sub Import()
'
' Import Macro
' Macro recorded 08/06/2008 by christopherr
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Y:\Financial Services\FS2008\Woodworkers\ER
Reports\052008\Test\Original Weyerhaeuser.TOC May 08.200806172100086.txt" _
, Destination:=Range("A1"))
.Name = "Original Weyerhaeuser.NelsonTrust.200806172055088"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Totals"
End Sub

Any suggestions/ideas would be great.

TIA
~Christopher

"Joel" wrote:

I don't like using recorded macro without making changes. The recorded
macros use "Selection". I pefer to specify Worksheet names and Ranges.

Set myRange = Application.InputBox(prompt := "Sample", type := 8)
with Sheets("Sheet2")
MyRange.Copy
.Paste 'really shoud have a range here. Can cause errors.
.Columns("E:E").EntireColumn.AutoFit
.Columns("H:H").EntireColumn.AutoFit
.Columns("A:A").EntireColumn.AutoFit
end with

Here is my modifed code with your amcro

Sub Worksheet()
'
' WorkSheet Macro
' Macro recorded 08/06/2008 by christopherr
'

'
Set myRange = Application.InputBox(prompt := "Sample", type := 8)
MyRange.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Columns("E:E").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Sheets("Totals").Select
Application.CutCopyMode = False
End Sub



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default VBA - UserForm Reset Option
There are towo functions shown below. The difference is the OPEN method
insists on a pre-existing file and the SAVEAS allows you to use either a
pre-existing filename or a new file name. Both return a Fullpathname along
with the filename.

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt), *.txt")
If fileSaveName < False Then
MsgBox "Save as " & fileSaveName
End If

fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If fileToOpen < False Then
MsgBox "Open " & fileToOpen
End If





"megatron08" wrote:

Thanks this is what I needed.

Can I create an Application.InputBox to prompt the user to find a file like
in the following macro? Is it even possible?

Sub Import()
'
' Import Macro
' Macro recorded 08/06/2008 by christopherr
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Y:\Financial Services\FS2008\Woodworkers\ER
Reports\052008\Test\Original Weyerhaeuser.TOC May 08.200806172100086.txt" _
, Destination:=Range("A1"))
.Name = "Original Weyerhaeuser.NelsonTrust.200806172055088"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Totals"
End Sub

Any suggestions/ideas would be great.

TIA
~Christopher

"Joel" wrote:

I don't like using recorded macro without making changes. The recorded
macros use "Selection". I pefer to specify Worksheet names and Ranges.

Set myRange = Application.InputBox(prompt := "Sample", type := 8)
with Sheets("Sheet2")
MyRange.Copy
.Paste 'really shoud have a range here. Can cause errors.
.Columns("E:E").EntireColumn.AutoFit
.Columns("H:H").EntireColumn.AutoFit
.Columns("A:A").EntireColumn.AutoFit
end with

Here is my modifed code with your amcro

Sub Worksheet()
'
' WorkSheet Macro
' Macro recorded 08/06/2008 by christopherr
'

'
Set myRange = Application.InputBox(prompt := "Sample", type := 8)
MyRange.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Columns("E:E").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Sheets("Totals").Select
Application.CutCopyMode = False
End Sub



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Reset Colors Option gone from Excel 2007? Jennifer Watnemoe Excel Discussion (Misc queries) 2 January 24th 08 06:09 PM
Auto advance to next field -tab key AND option button to reset fie ~KO Excel Programming 0 March 1st 06 09:35 PM
reset option buttons texicaliblues Excel Worksheet Functions 2 December 20th 05 06:17 AM
reset option buttons dominicb[_152_] Excel Programming 0 October 26th 05 11:58 PM
reset option buttons raw[_6_] Excel Programming 0 October 26th 05 11:19 PM


All times are GMT +1. The time now is 12:07 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement