Posts by mjschukas

Re: Fill Form's Combobox With Filtered Range

thank you...

the user form works well after the first time i load and close the form...??

the code i'm using follows:

Worksheets("data_material").Range("a2") = "tree"
ComboBox1.SetFocus

and in the properties of the combobox on the userform, i have set the rowsource to the range name ("data_materal").

???

thank you.

Re: Compress Worksheet File...

thank you for the thought and effort...

i just tried, no error message, but i also don't get the file compressed (it compresses, but with nothing in it...?

(here's what i changed--one line...:)
from

oApp.Namespace(FileNameZip).CopyHere oApp.Namespace(FolderName).items

to

FileCopy FileNameZip, DefPath & FolderName & FileName

thank you...

Re: Fill Form's Combobox With Filtered Range

well, i am using a dynamic range name (thanks to ozgrid...)

=OFFSET(data_material!$A$1,0,0,COUNTA(data_material!$A:$A),8)

and the following code--the range name changes, but not the filling of the combobox...?

Selection.AutoFilter 'turns off
myfilter = "Shrub"
Range("testmaterial").AutoFilter Field:=2, Criteria1:=myfilter
UserForm3.ComboBox1.RowSource = "data_material!testmaterial"

???

thank you...

Re: Compress Worksheet File...

ok...i'm thinking the compression works (above)...
and where would i use filecopy...?

???

thank you.

i have a range name (9 columns) and i filter for a certain type (Shrub) and i would like to only fill the combobox (on a form) with the filtered selection...?

myfilter = "Shrub"
Range("testmaterial").AutoFilter Field:=2, Criteria1:=myfilter
ComboBox1.RowSource = testmaterial

???

thank you...

i'm using the following code to compress a worksheet, but i get an error message that i would like to surpress...

'zip
On Error Resume Next
Dim FileNameZip, FolderName
Dim strDate As String, DefPath As String
Dim oApp As Object
    DefPath = "c:\temp\salesmanMDB\ed\"
    If Right(DefPath, 1) <> "\" Then
        DefPath = DefPath & "\"
    End If
FolderName = "c:\temp\salesmanMDB\ed\" '<< Change
FileNameZip = DefPath & "ed_CustomerItemHistory" & ".zip"
    'Create empty Zip File
    NewZip (FileNameZip)
    Set oApp = CreateObject("Shell.Application")
    'Copy the files to the compressed folder
    oApp.Namespace(FileNameZip).CopyHere oApp.Namespace(FolderName).items
    Set oApp = Nothing
End Sub


Sub NewZip(sPath)
On Error Resume Next
    If Len(Dir(sPath)) > 0 Then Kill sPath
    Open sPath For Output As #1
    Print #1, Chr$(80) & Chr$(75) & Chr$(5) & Chr$(6) & String(18, 0)
    Close #1
End Sub

???

thank you.

Re: Automating Data Filter Autofilter...

ok...whatever i filter for (say June or April, etc.) shows in the first row (i would like not to show/display this...)

???

thanks...!

Re: Automating Data Filter Autofilter...

thank you...getting better...

?the criteria shows in the first row...?can i now show the criteria (month in this case)...?

???

thank you.

Automating Data Filter Autofilter...

i would like to automate the autofilter (from a form)...the following code works, but...it needs improvement...

myfilter = Range("d2").Value
    Selection.Range("testmonth").Select
    Selection.AutoFilter Field:=1, Criteria1:=myfilter

???

thank you.

Re: Insert A Word Object In Excel, Type... And Resize...

well, maybe...

i'm inserting the word object in a worksheet...

???

thanks.

Re: Minimize File Size...

thank you...i'm reading and will try tomorrow...

thanks...!

insert a word object, type... and resize...

Dim objX As OLEObject
Set objX = ActiveSheet.OLEObjects.Add(ClassType:="Word.Document.8", Link:=False, DisplayAsIcon:=False)
    objX.Border.Color = RGB(255, 255, 255)
objX.Height = 200
objX.Width = 600
objX.Top = 300
objX.Left = 100
'-------make active
objX.Activate
    Set objX = Nothing

i would like to place the object in a worksheet and have all the rows of data move down below the word object (i.e., below the word object--i do this resizing manually now...)...?

???

thank you...!

Minimize File Size...

?how do i go through my workbook and worksheets and make sure that i am minimizing the file size...?

???

thank you.

autofitting on workbook opening...

i'm using on workbook_open:

columns("b:b").entirecolumn.autofit

but underneath, i have a data query range that refreshes and best fits the columns accordingly...(over writing the autofit...)?

???

thank you.

Re: Testing For First Letter Of Entry...

uhmmmmmmmmm.....

thank you...i thought i tried this...but it works....!

thank you...

Testing For First Letter Of Entry...

within an if statement, i'm testing for the first letter of another cell
with the function: left(e273,1)= "D"...

this works well, but i only have a few letters to test for and i'd like to test for not "D", etc. (i.,e, what if the first letter is not D, then do...)...?

???

thank you...

Re: Improve Error Handling...

here's my current attempt and "clean up"...

Private Sub Label1_Click()
'save to f:
On Error GoTo ErrorHandler
usf_SelectYear.Show
landbidyear = usf_SelectYear.cmbYear
customerName = Range("h6").Value
If IsEmpty(customerName) Or customerName = "" Then _
Err.Raise 56, customerName, "Missing customerName"


customerjobwork = Range("h7").Value
If IsNull(customerjobwork) Or customerjobwork = "" Then _
Err.Raise 57, customerjobwork, "Missing customerjobwork"


salesmanName = Worksheets("data_Salesman").Range("b2").Value
mysalesyear = Range("h4").Text
myfilename = Left(Trim(customerName & "-" & customerjobwork & "+" & mysalesyear), 31)
ActiveWorkbook.SaveAs filename:= _
        "\\03-server\data\kinsella\proposals\" & landbidyear & "\" & salesmanName & "\" & myfilename & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Unload Me
Application.WindowState = xlMaximized


ErrorHandler_Exit:
    Exit Sub
ErrorHandler:
Select Case Err.Number ' Evaluate error number.
    Case 55 ' "File already open" error.
        Close #1 ' Close open file.#
         Case Is >= 56, Is < 200
        MsgBox "Error# " & " " & Err.Number & " - " & Err.Description, vbCritical
        Resume ErrorHandler_Exit
    Case Else
        MsgBox "No special $%#&* characters in the file name...", _
        vbExclamation, "Problems"
    End Select
    Resume ' Resume execution at same
End Sub

Re: Insert Symbol ®

thank you...

i have hundreds of these Registered names to enter--the insert symbol works, but gets tedious...

i was looking for a shortcut to add the ® at the end of the data entry in a given cell...

???

thanks.

Re: Insert Symbol ®

sorry for any confusion...

as i entry a new word (in a cell), sometimes, i want to add the "®" at the end (of the new word--now always the same word...)

i seemingly can't run the macro from within the cell in edit mode...???

thank you...

i would like to automate the inserting of the symbol ®...

i can do in a cell as the first entry, but i can't do at the end of a string's entry in a cell...?

ActiveCell.FormulaR1C1 = "®"

or

ActiveCell.FormulaR1C1 = "Schukas®"

???

thank you.